• 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 Using Percent Range

T

test

New Member
Hi,
I am trying to use conditional formatting to do two separate actions:
1) color the cells either red, yellow, or green, and
2) use the red, yellow, and green stoplight icons
This will allow the user to choose which view they want to use.

This is using percentages as follows:
cell G1 is the current desired percentage
Cells E5 - E28 are the actual percentages of each item catalog item

If the actual percentage is less than plus/minus 10% of the current desired percentage, color the cell green or display the green stoplight icon

If the actual percentage is >= plus/minus 10% of the current desired percentage and < than plus/minus 20% of the current desired percentage, color the cell yellow or display the yellow stoplight icon.

If the actual percentage is >= plus/minus 20% of the current desired percentage color the cell red or display the red stoplight icon.

Example: current desired percentage in cell G1 is 50%

plus 10% of current desired percentage = 55%
minus 10% of current desired percentage = 90% of current desired percentage = 45%
plus 20% of current desired percentage = 60%
minus 20% of current desired percentage = 80% of current desired percentage = 40%

actual percentage of catalog item #1 in cell E5 is 45% color cell yellow
actual percentage of catalog item #2 in cell E6 is 63% color cell red
actual percentage of catalog item #3 in cell E7 is 50% color cell green
actual percentage of catalog item #4 in cell E8 is 41% color cell yellow
actual percentage of catalog item #5 in cell E9 is 53% color cell green
actual percentage of catalog item #6 in cell E10 is 40% color cell red
actual percentage of catalog item #7 in cell E11 is 46% color cell green
actual percentage of catalog item #8 in cell E12 is 55% color cell yellow
actual percentage of catalog item #9 in cell E13 is 54.9% color cell green
actual percentage of catalog item #10 in cell E14 is 45.1% color cell green
actual percentage of catalog item #11 in cell E15 is 44.9% color cell yellow
and so on down to cell E28

In other words, setting cell G1 to the current desired percentage of 50%
0 - 40% = red
40.1% - 45% = yellow
45.1% - 54.9% = green
55% - 59.9% = yellow
> 60% = red

And, as the current desired percentage changes up or down the red yellow and green percentage ranges change accordingly.

Here is what I have so far in the conditional formatting and I can get it to work ..somewhat..only some of the time:

=IF(OR(E17>G1*1.1, E17<G1*1.2),IF(OR(E17<G1*0.9, E17>G1*0.8),TRUE,FALSE),FALSE)
this was to color the cell yellow

=IF(OR(E17<=G1*1.1, E17>=G1*0.9),IF(OR(E17>=G1*1.2, E17<=G1*0.8),TRUE,FALSE),FALSE)
this was to color the cell red

=IF(OR(e17<=G1*1.1, e17>G1*0.9),TRUE,FALSE)
this was to color the cell green

I have tried changing the order of precedence and also tried using the Stop If True checkboxes without success. Thanks for the help. This should not be as difficult as I am making it out to be.
 
Hi ,

I am not sure whether you have to use the icon sets , or you merely wish to colour the cells ; can you check whether the uploaded file is OK ?

Narayan
 

Attachments

  • Example1.xlsx
    8.8 KB · Views: 16
Hi, The uploaded file is fine. The colored cells work nice. The icons are desired to help others as some believe colored cells is too much color.
 
SirJB7, thanks for the reply. I had already used the absolute value in my formulas and that was not the issue. I just had not used the absolute value in the thread when I wrote it. However, Narayan did use it in his solution. It works as desired. I am hoping the stop lights solution is forthcoming.
 
You must log in or register to reply here.
Back
Top