Conditional formatting on pivot table that will compare one column (column C) with previous column (Column B)

0
votes

enter image description here

Trying to do conditional formatting on pivot table that will compare one column (column C) with previous column (Column B) and if Column C is a percent higher/lower than Column B then the cell should turn Green or Red. Would like this to work across pivot (each column is weeks)."

I have tried using a formula =$C2>=$B2, but this will only work on the 2 columns selected and also only based on the numbers not a percentage

1
excelexcel-formulaconditional-formatting
so what would be the color if it is NOT a percent higher/lower? also are the column number fixed or dynamically change from time to time? – Terry W

1 Answers

0
votes

One approach could be this:

  1. I calculate the difference between the two values.
  2. Then I check if that difference is larger than 1%.
  3. I make it to a TRUE/FALSE statement as conditional formatting only accept this kind of return value.

So for green values:

=IF($C4<>"",OR((($C4/$B4)-1)>=0.01,$C4=$B4),FALSE)

To deal with empty values in column B, as 1/0 is not possible. 

=IF(AND(ISBLANK($B4),$C4<>""),OR((($C4/0.01)-1)>=0.01,$C4=$B4),FALSE)

And then for the Red Values

=AND((($C4/$B4)-1)<0.01,$C4<>"",$B4<>"")

See example below:

enter image description here