Hi,
Im trying to apply conditional formatting on my pivot table values' fields in order to get the right data bars set along the column. The thing is, the values are in negative order, because they represent savings... And when I add the data bars it naturally filters from the lowest to the highest value, which means it will get the max abs value as the lowest and the min abs value as the highest, because they are negative... And I want it the other way round instead. ideal.PNG This is the ideal format which is wanted...
One workaround I've thought about was binding the data bars rule to cells which have formulas getting the minimum and maximum value within the range filtered throughout the pivot, and therefore instead of setting Lowest-Highest rule, I set the number-number and look up the cells where the formulas are, closing the range and formatting... As it is a variable range, I dont know how to come up with a formula that restricts the range until the last value before the Pivot TOTAL is shown.
I have used this formula =MIN(I14:I33) on a locked range with no criteria and returns the minimum value, which is the highest saving in that case. Another formula im using to get the maximum value, which is the lowest saving is this one =INDEX(I14:I33;MATCH(2;(1/(I14:I33<0)))). I cant fix the last cell as I33, as the pivot is constantly being changed. The first one "I14" is ok...
If someone could help me Id really appreciate it. Hope I was clear enough!
Any other workarounds and solutions would be awesome!
Thanks in advance!
Bookmarks