Compare values with tolerance

[alombia]

Hi,

I have the following formula to compare tow values: =IF(BO4="","",IF(BO4<$BL4,"Poor",IF(BO4=$BL4,"Good",IF(BO4>$BL4,"Very Good"))))
As an example, BO4 is 1.5 and BL4 is 2.

However, I would like to compare the two values but with a tolerance of 0.5. In others, If BO4 is is with 0.5 of BL4, then 'Good'; less than that is 'Poor' and above that is "Very Good".

Your help is very much appreciated.
Thanks.

 

[alombia]

Hi,
That would for me in some circumstances. However, ideally i need it to be tolerance both ways, i.e. 0.5 below or above.

 

[admiral100]

So in case number 4 the result is good ?

 

[alombia]

So in case number 4 the result is good ?

Yes.

 

[admiral100]

Try this:

=LOOKUP(ABS(BL4-BO4),{-15,0,0.51},{"Very Good","Good","Poor"})

 

[alombia]

No, this simply returns 'poor' instead of 'very good'. Otherwise, it works as far as case 4 is concerned.

 

[admiral100]

I Don't understand ....case 4 is good and not very good

BL4 (Target)	BO4 (Actual)	Result
2	2	Good
2	1.5	Good
2	1.4	Poor
2	2.1	Good

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[alombia]

I Don't understand ....case 4 is good and not very good

BL4 (Target)	BO4 (Actual)	Result
2	2	Good
2	1.5	Good
2	1.4	Poor
2	2.1	Good

<tbody>
</tbody>

Yes, the above works. But with the new formula there is no 'Very Good'. Suppose case 5 is BO4 is 3. It returns 'poor'! In every case where BL4-BO4 is over 0.5, it returns 'poor' instead of 'Very Good'.

 

[admiral100]

I think I got you

=LOOKUP(BL4-BO4,{-15,-0.5,0,0.51},{"Very Good","Good","Good","Poor"})

 

[alombia]

I think I got you

=LOOKUP(BL4-BO4,{-15,-0.5,0,0.51},{"Very Good","Good","Good","Poor"})

Fantastic! It works brilliantly. Thank you.

Now, help me understand the values (-15,-0.5,0,0.51) so that I can try and make it dynamic. In other others, I want to put 0.5 in a cell and simply reference that cell so I can change the tolerance as i need. For example, 1 instead of 0.5 etc.
Once again, thanks for your patience and support.

 

[admiral100]

You are welcome.

When I started to explain to you, I noticed that the formula could be improved a bit ...

=LOOKUP(BL4-BO4,{-15,-0.5,0.51},{"Very Good","Good","Poor"})

The formula checks the difference between BL4 and BO4 :

Any value between +0.5 and -0.5 = good (ie -0.2 , 0 , 0.4 etc')
Above +0.5 = poor
Below -0.51 = Very good

Thank you for the feedback