Conditional Formatting With Nested If Statements [excel]

Question

I have a PivotTable that, for this example's sake, has three columns:

Column B contains non-negative number values.

Column D contains non-negative number values.

Column C shows the percent of change from Column D to Column B (ie, D1=1 and B1=2, C1=100%).

I have a Conditional Formatting rule set up so that if any value in Column C is greater than 10%, the cell is highlighted in red. However, this does not work when a cell in Column D has a value of zero, and the corresponding cell in Column B has a value higher than zero, because the result would be infinity.

My goal is to set up a rule so that if any cell in Column D has a value of zero, and any cell (on the same row) in Column B has a value greater than zero, the corresponding row in Column C will still be highlighted in red. Since I have non-negative numbers in my data, technically as long as Column D is zero and Column B is not zero, this would satisfy the formula.

I've tried using the following formatting rule to apply to Column C but have not been successful. I'm sure this could be also be accomplished using nested IF statements:

IF(D$=0 AND(B$<>0), TRUE, FALSE)

Answer 1

AND in Excel is used differently... it works like this: and(condition1,condition2). So your code should be formatted like this:

IF(AND(D$=0,(B$<>0)), TRUE, FALSE)

But it works differently in conditional formatting... so what you do is select the range, but make sure to note what cell is highlighted:

As you can see, you don't have to worry about the TRUE FALSE conditions... just the logical test is enough. So if you follow my screenshot, your formula should be:

=AND(D4=0,B4<>0)

(I assume your data starts in row 4... this should be the row that is HIGHLIGHTED in your selected range. In my case it's row 4.