• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting in which each row's aggregate value is evaluated by its own min/max

A

AitchK

Member
Hi Excel forum. Thanks for being here. I've attached an excel file.
My goal is to build a dashboard that will visually bring attention to when, for a given fruit group, the month's count is in the top or bottom 10% of what is typical. For example, highlight the cherries value 42/month 9, 20/month 7 and 40044/month 3, in yellow.
Since each group: apples and cherries have different bell curves, I need to apply conditional formatting that evaluates the values in respect to the fruit group's own calculated top/bottom 10% values.
I am willing to rework the whole solution as right now it involves building a pivot table, then doing calculations using excel functions to the right of that. It's not very robust: if the order of the row labels in the pivot were to sort differently upon refresh, the dependent hard-coded calculations would become inaccurate.
Can you offer some ideas or solutions for achieving this efficiently?
 

Attachments

Selecting your data range in the PivotTable (PT), you could apply this conditional formula:
=AND(ISNUMBER(B5),OR(B5<$Q5,B5>$P5))

This setup has you still having formulas in col N:Q. Looking at the formulas in N:Q, they are all row dependent, looking at the PT, so even if the PT gets sorted/refresh/rearranged, each row will be telling you information about the same row within the PT, and the PT's conditional formatting will work correctly.

OR...

You can define a named range called "DataRow" by selecting cell B5, and then setting up the named range with definition:
=dist!$B5:$M5

Then you can write a conditional formula of:
=AND(ISNUMBER(B5),OR(B5<=NORM.INV(10%,AVERAGE(DataRow),STDEV.P(DataRow)),B5>=NORM.INV(1-10%,AVERAGE(DataRow),STDEV.P(DataRow))))
the benefit being that now you don't need the other formulas in N:Q.
 
Yes, Luke M, I just used solution one and I am able to apply different conditional rule to each row. I will try solution two in a couple of hours and pretty optimistic. Thanks.
Will you tell me if there is a way to have only two colors in the conditional formatting? I want only green if within range and yellow if outside (not gradiations of green to yellow).
 
Copy/Create a new CF rule, and change the formula from:
=AND(ISNUMBER(B5),OR(...))
to this:
=AND(ISNUMBER(B5),NOT(OR(...)))
 
OK, thank you!!! I added the advised NOT and applied the color I wanted if that condition were true.
Excellent. thanks to you guys, very much.
 
Last edited:
Update, The formulas you provided work great, but just having trouble defining separate ranges for each named range. I have one called ApplesRow, defined for =dist!$B5:$M5, but when I created a second one CherriesRow for =dist!$B6:$M6, then the ApplesRow is changed to =dist!$B6:$M6 as well!
I've attached my file. Can you show me how I can create
ApplesRow =dist!$B5:$M5
CherriesRow =dist!$B6:$M6
 

Attachments

You don't need to create separate named ranges/formulas. That was the beauty of solution. :)
By having the row be a relative reference in the Named Range, each row in your PivotTable automatically looks at only it's row of numbers. :DD
The attached file shows the example of this + green format. Hopefully it makes sense.
 

Attachments

You must log in or register to reply here.
Back
Top