Cell Value by Result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find the best way to develop a simple formula which sets the multiplier cell based on specific ranges of values. Eg. If efficiency is 77% to 82% multiplier would be $.50. 82.01% to 87% would be $ 1.00 and so on. Could use a nested if statement but am hoping that there would be a better way.
 
One way

=VLOOKUP(A2,{0,0;0.77,0.5;0.821,1;0.871,1.5;0.921,2;0.971,2.5},2)

I assumed under 77%, zero multiplier
if you want more you can just add it the same way, delimit with ; then next
percentage, then comma, then multiplier for this
particular range and so on

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



Moz said:
I am trying to find the best way to develop a simple formula which sets
Click to expand...
the multiplier cell based on specific ranges of values. Eg. If efficiency
is 77% to 82% multiplier would be $.50. 82.01% to 87% would be $ 1.00 and
so on. Could use a nested if statement but am hoping that there would be a
better way.
 
=IF(A1="","",CHOOSE(MATCH(A1,{0;0.77;0.82;0.87;0.92;0.97},1)-(A1>0.77)*(LOOK
UP(A1,{0;0.77;0.82;0.87;0.92;0.97})=A1),0,0.5,1,1.5,2,2.5))

The 1-column tables the formula uses are:

0 0
0.77 0.5
0.82 1
0.87 1.5
0.92 2
0.97 2.5


Moz said:
I am trying to find the best way to develop a simple formula which sets
Click to expand...
the multiplier cell based on specific ranges of values. Eg. If efficiency
is 77% to 82% multiplier would be $.50. 82.01% to 87% would be $ 1.00 and
so on. Could use a nested if statement but am hoping that there would be a
better way.
 
Back
Top