Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Im currently using the following expression to shade the 'value' cell of a pivot chart Red for below tolerance and green for within tolerance:
=if(not isnull(tolerance),if(If(single_value='N',(sum(val1)/sum(val2)*100), sum(val3)) <tolerance,RGB(255,0,0),RGB(0,255,0)))
What I need to add is an Amber shade. This is where the value calculation is within 90-99% of the 'tolerance' value. Red would then need to be below 90% of the tolerance value. Is this possible to do with my current expression?
Many thanks,
Fiorano
That's it
=
if(not isnull(tolerance),
if(If(single_value='N',(sum(val1)/sum(val2)*100),sum(val3)) <tolerance,
RGB(255,0,0),
if(If(single_value='N',(sum(val1)/sum(val2)*100),sum(val3)) >=tolerance and If(single_value='N',(sum(val1)/sum(val2)*100),sum(val3)) <=99,
'Amber',
RGB(0,255,0)
)
)
)
Hope it helps
Hi, thanks for your reply. For some reason it has only worked for 1 row in my pivot (where the tolerance value is 85%). All of the other tolerance values are set at 100%.... Any ideas?
Many thanks again,
Fiorano
Could you provide an example?
Hi,
Ive attached an xls copy. If you look at 'Item 13' (row 15) there are values that fall between the 90%-99% range of the 100% tolerance which should be 'amber' but they are kept at red.
Another one is 'Item 5'
Hope this helps,
Many thanks again,
Fiorano
I'm sorry but I can't answer without looking at the document ...
Hi Sorry for the late reply.
I've attached a sampel file where I am formatting the cell to green or red.
As explained in previous posts. If the calculated value is :
Ive added a 'Rating LookUp' Table to try and simplify the expression - not sure if this helps in any way???
Really appreciate your time in this!
Fiorano
See attached qvw.
Awesome !! This is perfect! Many thanks for your help.
Fiorano
Hi,
Thanks again for your previous answer. I've noticed that some questions are measured by being over target. For Example:
Pseudo Code
If Question_number in (1,2,3,4) then red or amber if value is less than Target and Green if value is above Target
If Question_number in (5,6,7,8,9,10) then red or amber if value is above Target and Green if value is less than Target.
Are you able to modify your previous solution?
Many thanks,
Fiorano