Color when an undefined value is there in a cell

[#1]

Hi,

I have a pivot table where expression C = A/B; when B=0, it shows '-'. Its perfectly fine.

I want background color of the cell based on values of C..

if C<20%, red,

if 20%<=C<25%, Amber,

if C<=25%, Green,

if Undefined, Yellow,

I used the following expression -

=if(C<.2,red(),if(C>=.25,green(),if((C>=.2 and C<.25),rgb(255,183,255),yellow()))).

Two problems I am facing :-

1. Amber color is not proper, actually for that I want to use {rgb(255,183,255) hsl(200,240,206)}...but, am not able to use both ?

2. For undefined values, its not putting in Yellow..

Any idea ??

[manideep78]

Hi try this,

If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C<=0.25, Green(),if(C='' or C=null(), Yellow()))))

If possible please attach the sample App

Regards,

Manideep

[#3]

The Yellow stuff is not working..actually, the value is not null rather it is undefined and tht's why, its not getting the condition for Yellow..

[#4]

Try like

if(C = '-',yellow())

--Singh

[manideep78]

Can you please give me the sample App

[#6]

Try putting Yellow on Else condition..

something like...
If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C>0.25, Green(),Yellow()))))

or what  Angad Singh suggested.

Regards,
Dawar

[manideep78]

If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C<=0.25, Green(),if(C='-' , Yellow()))))

OR

If(C<0.2,red(),if(C>=0.2 and C<0.25,RGB(255,191,0),if(C<=0.25, Green(),Yellow())))

[qlikoqlik]

HI

Please see below code I have used in the background of the expression

if((sum(ColorA)/sum(ColorB))<0.2,red(),
if((sum(ColorA)/sum(ColorB))>=0.2 and (sum(ColorA)/sum(ColorB))<0.25, rgb(255,183,255),
if((sum(ColorA)/sum(ColorB))>=0.25, Green(),
Yellow())))

Regards

Padma

[#9]

Can u try in case of Pivot ?..Yellow stuff is still not working for me ..:-(

[qlikoqlik]

Hi Vimal

It is working for me on Pivot table as well

if you can give me some sample data on excel I can try

Thanks

Padma