Between Values

[LEVICC01]

Hi all,

I think i'm going mad.

I have a forecasted Volume, and an actual volume.

I need a formula that look sat the forecast, and compares to the volume.

If the forecast is within 5% either side of the volume, i want to return the value of 3,
If the forecast is within 7.5% either side of the volume, i want to return the value of 2,
If the forecast is over 7.5% either side of the volume, i want to return the value of 1.

Please can someone help?

 

[VoG]

Maybe, forecast in A1, actual in B1

=LOOKUP(ABS((B1-A1)/B1),{0,0.051,0.075},{1,2,3})

 

[JamesW]

Hi,

Try:

=IF(AND(B2>=(A2-A2*0.05),B2<=(A2+A2*0.05)),3,IF(AND(B2>=(A2-A2*0.075),B2<=(A2+A2*0.075)),2,IF(B2>(A2+A2*0.075),1,"NA")))

 

[pgc01]

Hi

Also:

=MATCH(ABS(B1/A1-1),{1E+300,0.075,0.05},-1)

 

[LEVICC01]

Thanks Both,

Couldnt get the lookup to work, but the second, long one did.
I had to remove IF(B2>(A2+A2*0.075),1,"NA"))) and just put 1 instead so anything not in the first 2 would return a 1 and this worked perfect.

This is more or less what i had, but i didnt trust my logic. Thanks both.