Comparing two or more negative numbers

[silver_john]

Hello Forum,

I'm desperately looking for help here.

I'm trying to set up Variance as a KPI for my team. The Variance percentage is in column F. So, if the Variance is less than +/- 1 then E column should return 5. If Variance is greater than or equal to +/- 1 but less than +/- 2, it should be 4 and anything greater than +/- 2 it should return 3.

Pradeep

 

[Joe4]

Welcome to the Board!

If you were checking the value in cell F2, try this formula:

=IF(ABS(F2)>2,3,IF(ABS(F2)>1,4,IF(ABS(F2)>=0,5,"")))

 

[silver_john]

Thank you for the quick response Joe4.

But this didn't get me the expected results. You see, the Variance score could be a positive or a negative value, while 99% of the time it would be a negative value. So when applying this formula it returns "5" for all the cells.

100.00%	100.00%	0.00%	5
95.00%	95.00%	0.00%	5
100.00%	100.00%	0.00%	5
100.00%	100.00%	0.00%	5
100.00%	100.00%	0.00%	5
90.00%	90.00%	0.00%	5
60.00%	60.00%	0.00%	5
100.00%	100.00%	0.00%	5
100.00%	100.00%	0.00%	5
100.00%	100.00%	0.00%	5
100.00%	100.00%	0.00%	5
100.00%	90.00%	-10.00%	5
95.00%	90.00%	-5.00%	5
100.00%	100.00%	0.00%	5
100.00%	100.00%	0.00%	5
80.00%	75.00%	-5.00%	5
100.00%	100.00%	0.00%	5
90.00%	80.00%	-10.00%	5
100.00%	95.00%	-5.00%	5
100.00%	100.00%	0.00%	5
100.00%	90.00%	-10.00%	5
100.00%	100.00%	0.00%	5
100.00%	95.00%	-5.00%	5

Thanks in advance.

 

[Joe4]

Thanks because you didn't specify in your original post that you were dealing percentages!
Note that 1% is NOT the same as 1.
1% actually equals .01.

So you need to amend the formula like this:

=IF(ABS(F2)>.02,3,IF(ABS(F2)>.01,4,IF(ABS(F2)>=0,5,"")))

If this still does not give you what you need, please post an actual data example of your data, along with the your expected results, making it clear which column the variance we are checking is located in.

 

[silver_john]

Excellent!! Sorry for missing that detail earlier. This works!

Thanks once again.

 

[Joe4]

You are welcome.
Glad I was able to help!