IF Function Formula with tolerance relative to whether benchmark is pos/neg

[NickPlanning]

Hi All,

I have a list of performance percentage figures which I need to give a score of 0, 1 or 2 depending on whether they improve on a benchmark or not.


If a figure is greater than the benchmark figure, a score of 2 is returned. If the figure is lower than the benchmark figure but within 10%, a score of 1 is returned. If the figure is lower outright after factoring in the 10% tolerance, a score of 0 is returned.


I had managed to achieve this using the following formula: =(IF(B1>$B$25,"2",IF(B1>($B$25*0.9),"1","0"))), with B25 being the benchmark figure. However, when the benchmark figure is a negative integer, the formula does not function until the "0.9" is amended to "1.1". Is there a simpler way of doing this that doesn't involve amending the formula relative to whether the benchmark is negative/positive?


As a worked example. If the benchmark was -2.5 the below figures would score the figure in brackets:


5 (2)
2.5 (2)
-2.5 (1)
-2.4 (2)
-2.65 (1)
-4 (0)

Many thanks in advance!

 

[NickPlanning]

IMAGE: https://ibb.co/nMrD2Yk

I've tried adding this formula into another

spreadsheet but it now does not function correctly.

In the attached image, the benchmark is cell G9 and the other figure is G10. Obviously 13.04 is bigger than 9.9, so the formula should return a 2, but it is returning a 1.

Can't for the life of me see why. Is there anything obvious?

Many thanks