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!

 

[oldbrewer]

=IF(B$2<0,IF(B2>B$2,2,IF(B2<b$2*1.1,0,1)),if(b2 style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">=B$2,2,IF(B2<b$2*0.9,0,1)))


you cannot say if b2>b2</b$2*0.9,0,1)))
</b$2*1.1,0,1)),if(b2>

 

[NickPlanning]

=IF(B$2<0,IF(B2>B$2,2,IF(B2<b$2*1.1,0,1)),if(b2 style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">=B$2,2,IF(B2<b$2*0.9,0,1)))


you cannot say if b2>b2</b$2*0.9,0,1)))
</b$2*1.1,0,1)),if(b2>

Because it refers back to itself? I'm confused as to why the results aren't consistently 1, 2 or 0 when the B2 compares itself back to B2 or C2 to C2 etc.

 

[oldbrewer]

say B2 was 8

you cannot say.....if 8 is bigger than 8 then do something...............

you are comparing scores with a target

so if score greater than target return a 2
if score less than target but within 10% of target return a 1
in all other cases return a zero

 

[NickPlanning]

say B2 was 8

you cannot say.....if 8 is bigger than 8 then do something...............

you are comparing scores with a target

so if score greater than target return a 2
if score less than target but within 10% of target return a 1
in all other cases return a zero

This is perhaps where it may get complicated. In this instance it doesn't matter as the benchmark row will never be used or relevant. However, what if one of the other figures was in this instance an 8 or was the same as the benchmark. In this task I would need it to return a 1.

Also, if the benchmark less 10% is the same as a figure, it would need to return 0.

Are these going to be possible?

 

[oldbrewer]

if benchmark is 8 then 8.1 scores 2

it is up to you to define whether a score of exactly 8 is a 1 or a 2
say A1 = benchmark = 8 and B1 is the score = 8 and you want 8 to score 2

=if(B1>=A1, 2, if..............code to detect if between 7.2 and 7.999

 

[NickPlanning]

Forgive my ignorance, but how would I amend the below formula to also include a score of "1" if B2 = B25?

The formula is in the "reason for editing" on this post. It isn't showing properly when i paste it into the forum post.

 

[NickPlanning]

I can't get the formula to show properly - it keeps cutting it short...

I've put it in the "reason for editing" on this post.

 

[oldbrewer]

put a space either side of < and >

 

[NickPlanning]

put a space either side of < and >

=IF(B$25 < 0,IF(B2 > B$25,2,IF(B2 < B$25*1.1,0,1)),IF(B2 > =B$25,2,IF(B2 < B$25*0.9,0,1)))

Hopefully this works. Trying to add a clause that if B25 = B2 it returns a 1. How would I do that? Thanks so much

 

[oldbrewer]

=if(b25=b2,1,IF(B$25 < 0,IF(B2 > B$25,2,IF(B2 < B$25*1.1,0,1)),IF(B2 > =B$25,2,IF(B2 < B$25*0.9,0,1))))