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!

 

[gaz_chops]

Welcome to Mr Excel,

Take a look at formula ABS
[FONT=&quot]Returns the absolute value of a number, a number without its sign.[/FONT]

 

[NickPlanning]

Hi. Thanks for the swift reply.

I did see that in a similar thread and began to investigate, but I can't for the life of me figure out how to incorporate it into the formula.

 

[gaz_chops]

Try

=(IF(B1>ABS($B$25),2,IF(B1>ABS($B$25*0.9),1,0)))

I removed the Quotes from the numbers, adding quotes causes them to become text not values.

 

[oldbrewer]

						BM	-5
	score	award
	-3	2	#####
	-3.2	2
	-3.4	2
	-3.6	2
	-3.8	2			######
	-4	2
	-4.2	2			=IF(B7>$H$4,2,IF(AND(B7<$H$4,ABS(B7)<ABS($H$4)*1.1),1,0))
	-4.4	2
	-4.6	2
	-4.8	2
	-5	0
	-5.2	1
	-5.4	1
	-5.6	0
	-5.8	0
	-6	0
	-6.2	0
	-6.4	0
	-6.6	0

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[oldbrewer]

ignore my last post please

 

[NickPlanning]

Try

=(IF(B1>ABS($B$25),2,IF(B1>ABS($B$25*0.9),1,0)))

I removed the Quotes from the numbers, adding quotes causes them to become text not values.

Hi Gaz,

I've tried that and it still doesn't seem to function when the benchmark is negative. Because it isn't perhaps then turning the ABS benchmark back to a negative figure.

i.e. if the benchmark was -10 and B1 figure was 5. It should score a 2 as 5 is higher than -10. However, the figure post-ABS is 10*0.9 = 9. So 9 is higher than 5 and it scores a 0.

 

[gaz_chops]

Try

=IF(A27>$B$25,2,IF(AND(A27>=$B$25,A27<($B$25)*0.09),1,0))

Are you sure the result for -2.65 is correct? Above returns 0 not 1!

 

[oldbrewer]

						BM	-7
	score	award
	-5.6	2	#####
	-5.8	2
	-6	2
	-6.2	2
	-6.4	2			######
	-6.6	2
	-6.8	2			=IF($H$4<0,IF(B7>$H$4,2,IF(B7<$H$4*1.1,0,1)),IF(B7>=$H$4,2,IF(B7<$H$4*0.9,0,1)))
	-7	1
	-7.2	1
	-7.4	1
	-7.6	1			as a challenge I did it without using the ABS function
	-7.8	0
	-8	0
	-8.2	0
	-8.4	0
	-8.6	0
	-8.8	0
	-9	0
	-9.2	0

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[NickPlanning]

Thank you very much. That formula does seem to work. Except for when I get to the row which has all the benchmark figures in. I would expect every single score to be 1, as the benchmark looks at itself and returns a score of 1. But it doesn't seem to do that every time. I can't see why.

10

<tbody>
</tbody>

A1	B1Figure	C1Score	Figure	Score	Figure	Score	Figure	Score	Figure	Score	Figure	Score	Figure	Score
A2 Bench	B2-1.79	1	-1.33	1	10.47	2	3	1	3.49	1	8.49	2	4.54	2

<tbody>
</tbody>

Score C2:
=IF(B$2<0,IF(B2>B$2,2,IF(B2<b$2*1.1,0,1)),if(b2>=B$2,2,IF(B2<b$2*0.9,0,1)))

Score E2:
=IF(D$2<0,IF(D2>D$2,2,IF(D2<d$2*1.1,0,1)),if(d2>=D$2,2,IF(D2<d$2*0.9,0,1)))

Score G2:
=IF(F$2<0,IF(F2>F$2,2,IF(F2<f$2*1.1,0,1)),if(f2>=F$2,2,IF(F2<f$2*0.9,0,1)))

As you can see there is no obvious difference as to why it would return a 2 for 10.47 but a 1 for the others.

Any ideas? Apologies for the convoluted way of showing it.</f$2*0.9,0,1)))
</f$2*1.1,0,1)),if(f2></d$2*0.9,0,1)))
</d$2*1.1,0,1)),if(d2></b$2*0.9,0,1)))
</b$2*1.1,0,1)),if(b2>