• 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

  • Counts_Outliers.xlsx
    813.5 KB · Views: 3
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

  • Counts_Outliers.xlsx
    813.8 KB · Views: 4
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

  • Counts_Outliers fixed.xlsx
    880.3 KB · Views: 6
You must log in or register to reply here.
Back
Top