Conditional formatting (icons) applied to a range using formulas

[dave0]

Greetings - I'm using Excel 2011 for Mac.

My watered down example consists of tracking "start" and "end" over a period of 7 days. So not including headers/labels there are 2 rows (B2:I3) and 7 columns. The data are all integers. I want to compare the value of B2 to B3 and display a "check" icon in the cell with the greater value and an "x" icon in the other. i'd also like to display the "!" icon if they happen to be equal. When I create a rule and apply it to B2 only, the "check" and 'x" icons display as expected.




My goal is to apply this rule to the entire range (B2:I2) and have the values being compared iterate to the end of the range. Unfortunately, when I apply it to the range, each successive value is compared to the first value tested ($B$3) and thus the icons do not display properly. I also don't see any way to specify that the "!" icon is to be displayed when the values are equal. Am I missing something?

I tried putting =B3 in the formula but get an error saying that relative references are not allowed. I know I can create a separate rule for each cell, but I find it very hard to believe that this would be the only way to go.


And finally, I find the 3 sections of the icon set rule dialog to be a little confusing. For the section relating to the "!" icon, it says

When value is < Formula and

Which formula is it referring to?




Similarly, the section for the "x" icon reads:

When value is < Formula

Which Formula is THIS one referring to?

 

[FDibbins]

You can either use the built-in CF rules - and use whichever rules have icons available to them (Format Cells Based On Their Value is 1 of those), or you can use the Use Formula rule. However the Use Formula rule does not allow the use of icons, only colors

 

[dave0]

You can either use the built-in CF rules - and use whichever rules have icons available to them (Format Cells Based On Their Value is 1 of those), or you can use the Use Formula rule. However the Use Formula rule does not allow the use of icons, only colors

I can't seem to figure out how to use the built in CF rule you mention to format the cell based on whether its value is > or < another cell's value. Is this possible?

 

[FDibbins]

You could use the Format Only Cells That Contain, then use the "between" boxes to set your upper and lower limit

 

[dave0]

You could use the Format Only Cells That Contain, then use the "between" boxes to set your upper and lower limit

ok so that works but it's not available for icon sets. is that correct?

 

[FDibbins]

ok so that works but it's not available for icon sets. is that correct?

Correct, sorry