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.

 

[admiral100]

Hi,

Try this:-

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

 

[alombia]

Hi,

Try this:-

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

Thanks but it is not producing correct outcomes. It should provide the following.

BO4 (Actual)	BL4 (Target)	Outcome
1.5	2	Good
1	2	Poor
0.5	1	Good
1.5	1.5	Good
2	1	Very good

<tbody>
</tbody>

 

[admiral100]

By what I understood from your explanation in post 1, if BO4 is 50% out of BL4 then GOOD less POOR and above VERY GOOD.


So do not understand your table:

BO4 (Actual)	BL4 (Target)	Outcome
1	2	Poor
0.5	1	Good

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

In both lines BO4 is 50% from BL4
One time IS POOR and one time is GOOD


Please try to explain again ....

 

[alombia]

Not 50% but the literal value of 0.5. I think that is the misunderstanding.

 

[admiral100]

Ok

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

 

[alombia]

Ok

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

Works like a treat. Thanks very much. Though the -15 was probably a typo. I replaced with -0.5.

 

[admiral100]

You are welcome , thank you for the feedback.

-15 not a typo, it was in order to deal with cases that BO4 (Actual) significantly larger then BL4 (Target)

 

[alombia]

Thanks.
I have been using your formula for a while and now notice that it doesnt actually work accurately. Fr example, your formula gives the following results:

BL4 (Target)	BO4 (Actual)	Result
2	2	Good	Right result
2	1.5	Good	Right result
2	1.4	Poor	Right result
2	2.1	Very Good	Wrong result.

<tbody>
</tbody>

Also, is there any way to change the formula so that instead of writing the values, i.e. -15,0,0.51, I can simply put the value in a cell and reference that cell. This way I can change the tolerance if required. Thanks again for the help.

 

[admiral100]

Hi,

I understood from you that if BO4 (Actual) is bigger then BL4 (Target) it's Very Good ....am I wrong ?