Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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