Excel Conditional Formatting Help

Question

I had a quick question, I have 2 cells in a spreadsheet row. If their difference, expressed as a percentage, is greater than zero but less than 9, I would like them to be yellow. Anything over 9 would be shaded in red. I looked at conditional formatting and this appears to be correct, but I can't seem to nail down the syntax. I also would like both the cells to be tested for zero values. If the cells are zero, I do not want to apply a format. Can anyone get me started with this?

I had something like =IF(A1 > 0 & B1 > 0, IF(ABS((A1-B1)/ABS(B1))>=1 && ABS((A1-B1)/ABS(B1))<=9, 'Some code to color the cell yellow' etc....

This obviously doesn't work right. Any idea how I ca get the desired effect?

Thanks to all who help, ~ck in San Diego

Answer 1

One way to do this would be to put your IF formula in an adjacent cell in the row, and have it resolve to something categorical which represents your conditions, such as 'Y' for yellow or 'R' for red.

Then use conditional formatting on both your cells, using the 'Formula is' option, and point to this calculated cell with Y or R in it to determine your shading using 2 conditions. You could then hide the calculated column.

Alternatively, you could put the actual formula in the 'Formula is' field in the conditional formatting dialog, but you'd need it to resolve to 0 (false) or 1 (true) for the format to apply in each condition.

Answer 2

Under Conditional Formatting / Manager Rules / Show Formatting rules for This Worksheet:

Add two new rules exactly as shown in the image:

The two formulas, which are obscured in the image, should be:

Top formula (I put in <=9% so there is no gap at exactly 9%):

=AND(ABS($A$1-$B$1)>0,ABS($A$1-$B$1)<=0.09)

Bottom formula:

=ABS($A$1-$B$1)>0.09

If you want to perform this conditional formatting down a column of A/B values, do not anchor on the row (i.e., only anchor on the column) in the formulas. Of course, you would also have to extend the anchored Applies to range to include all cells you want formatted.