Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have a pivot table with some numerical values and I need some formula in order to highlight them either green or red. What I need to do is to calculate if the numbers in the rows under the first one (the first row stays in place and it's always green) are either +- 5% of the number in the first row that's above it. How do I do this?
Thanks,
Adrian
PS: I have the free version, so I'm not able to open any file.
The same formula, but it works in a simple example:
If (Value<Top(Value)*1.05 and Value>Top(Value)*0.95, green(), red())
Hi Adrian,
In the properties of Pivot table, there is a tab called "Visual Cues".
You can apply colors to the expression here according to your requirement.
Hope this helps.
Thanks
Sabal
Hi,
I'm aware of this, I don't know how to get the first number from every column.
Hi,
I suppose you need to make calculated backgound of your cell in pivot table.
If it is, Chart-> Properties->Expressions-> "Expand + in expression"->Backgound color->Definition
If(condition, red(), green())
Hope this helps.
Andrei
Hi Adrian,
In the expressions tab you can expand the expression and there you can set the colors for background and font.
You can use something like:
If (YourExpression>Above(YourExpression)*1.05, green(),
If (YourExpression<Above(YourExpression)*0.95, red()))
in background color part try to add a expression like
if(sum(sales)>(above(sum(sales))+above(sum(sales))*.05), green(),
sum(sales)<(above(sum(sales))+above(sum(sales))*.05),red()))
-Sundar
Ok, this is a bit helpful, but what I'm trying to do is this:
Let's say this is a column: 100, 101, 106, 102, 98
100 is always green, 101 is green (because it's in the range 100 and 100+5%), 106 is red(not in the range), 102 is green(in the range), 98 is green (it's in 100 and 100-5%)
Is this a bit more clear? I might have not explained myself good in the first place.
Thanks
If u always want to refer to the first row then u can replace above() with first().. This will always look for 100in the eg that u have specified..
-Sundar
The maybe the expression should be like this:
If (YourExpression<Top(YourExpression)*1.05 and YourExpression>Top(YourExpression)*0.95, green(), red())
use backgroung color or text color in expresion