Conditional Formatting With Nested If Statements [excel]

0
votes

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)
2
excelexcel-formulaexcel-2013conditional-formatting
Is this a VBA question or do you require a Conditional Formatting formula? – Skip Intro
This does not make sense. You firstly ask that the cell should be highlighted red if cell c is greater than +10%, and you say this is your goal. You then say that you want it to be highlighted red if D is 0 and and B is greater than 0 it also be highlighted red. This would change your criteria to: column C >+10% AND column C<0%. As per @SkipIntro, this is conditional formatting question – user1
@skipIntro Conditional Formatting seems like the most straightforward approach, but if there's a way to do this in VBA I am open to using that as well. – Keefer
Also i tried this: conditional format via formula: Formula: = $C1>10% – user1
Try this formula "=And(D1=0,B1>0)". Before doing make sure you select the relevant column C range. If the first row is not C1 then adjust formula accordingly. – SJR

2 Answers

3
votes

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: enter image description here

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.

0
votes

Conditional formatting of column C, rows 1 to 50:

Applies to: =$C$1:$C$50

Format values when this formula is true: =AND($D1=0, $B1>0)

If your line separator is semicolon, not comma use this instead: =AND($D1=0; $B1>0)