why is excel giving me wrong replies for negative values?

[brockk]

Greetings to All,

I am trying to calculate a percentile value on some KPI's for my co-workers. Based on these criterias, it should either provide me the following replies:

A- if value is greater than 0.00% then the reply should be "Great Job!"
B- if the value is equal to or between the values of 0.00% and -0.05% (negative 0.05%) then the reply should be "Almost there"
C- Any value less than -0.05%, should indicate "Needs Improvement"

I am using the following formula: =IF(E25>0,"Great Job!",IF(AND(E25<=0,E25>="-0.05"),"You're almost there","Need Improvement"))

It seems to work in most cases but, those cases that are among the (B) criteria, (<=0 and is >="-0/05") is not calculating correctly as these also appear as "Needs Improvement". Any help to this matter is greatly appreciated.

 

[RoryA]

Remove the quotes around "-0.05" as that makes it text.

 

[brockk]

Thanks for the reply RoryA.

I did just that and it still is not giving me the appropriate response as you can see in the image shown.

weekly performance review form SQ1-AUGUST-2021.xlsx
	C	D	E
16	Exp EE	EE Conv	Differ in EE
17	1.00%	1.44%	0.44%
18	1.00%	0.33%	-0.67%
19	1.00%	0.88%	-0.12%
20	1.00%	0.66%	-0.34%
21
22	Exp TFB	TFB Conv	Differ TFB
23	0.16%	0.00%	-0.16%
24	0.16%	0.00%	-0.16%
25	0.16%	0.11%	-0.05%
26	0.16%	0.11%	-0.05%
27
28
29	Great Job!
30	You're almost there
31	You're almost there
32	You're almost there
33
34	You're almost there
35	You're almost there
36	You're almost there
37	You're almost there
PTRKR1

Cell Formulas
Range	Formula
D17:D20	D17	=INDEX(DATA1,MATCH($A17,Names,0),MATCH(D$16,$B$1:$AO$1,0))
E23:E26,E17:E20	E17	=D17-C17
D23:D26	D23	=INDEX(DATA1,MATCH($A23,Names,0),MATCH(D$22,$B$1:$AO$1,0))
C29:C32	C29	=IF(E17>0,"Great Job!",IF(AND(E17<=0,E17>=-0.05),"You're almost there","Need Improvement"))
C34:C37	C34	=IF(E23>0,"Great Job!",IF(AND(E23<=0,E23>=-0.05),"You're almost there","Need Improvement"))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
DATA1	=PTRKR1!$B$2:$AO$12	D17:D20, D23:D26
Names	=PTRKR1!$A$2:$A$12	D23:D26, D17:D20
SHIFT1	=PTRKR1!$A$17:$B$20	D17

 

[RoryA]

-0.05 is not the same as -0.05% (it's 100 times larger). You need -0.0005 or use -0.05%

 

[brockk]

Thank you soooooooooooo much RoryA. That did it! Such a small detail like that made such a big difference... lol!