Color code expression is not working with upper/lower limit

[gayatri7]

Hi All,

I have used below expression for color code based on AVG(SCORE). But it seems that whenever value is falling exactly on either upper or lower limit, color code is not working as expected.

IF(AVG(SCORE)>= 1.00  and AVG(SCORE) <= 3.79, RGB(255,153,129),
IF(AVG(SCORE)>= 3.80 and AVG(SCORE) <= 4.44,RGB(255,230,153),
IF(AVG(SCORE)>= 4.45 and AVG(SCORE) <= 4.69, RGB(198,224,180),
IF(AVG(SCORE)>= 4.70 and AVG(SCORE)<= 5.00 , RGB(112,173,71)))))

Example: If the AVG(SCORE) = 1 or 3.79 or 3.80 or 4.44 or 4.45 or 4.69 or 4.70 or 5.00 , it is not applying any color. For rest of the value with above ranges , it applies the color correctly.

Sample below. (Refer: Column 3 , Overall Avg Score)

Could you please help what mistake I am doing in this.

Thanks in Advance.

Gayatri

[Lisa_P]

It could be that your value falls in between these, but is displayed to 2 decimal places,

ie it falls between 4.44 and 4.45

[gayatri7]

Hi,

Yes, I did try rounding  off the the value to 2 decimal before applying color logic. And used below expression 

IF((NUM(AVG(SCORE),'#.##')>= $(vLRangeRed) and NUM(AVG(SCORE),'#.##') <=$(vURangeRed)), RGB(255,153,129),
IF((NUM(AVG(SCORE),'#.##')>=$(vLRangeAmber) and NUM(AVG(SCORE),'#.##') <=$(vURangeAmber)),RGB(255,230,153),
IF((NUM(AVG(SCORE),'#.##')>=$(vLRangelightgreen) and NUM(AVG(SCORE),'#.##') <=$(vURangelightgreen)), RGB(198,224,180),
IF((NUM(AVG(SCORE),'#.##')>= $(vLRangeDarkgreen) and NUM(AVG(SCORE),'#.##')<= $(vURangeDarkgreen)) , RGB(112,173,71)))))

Still it is not working as expected.

is there any other way to do so?

Thanks

Gayatri