Complicated Forumla Help

[fgbdrum]

We are computing some incentive plan payouts. Here's my scenario. Depending
on what percentage we hit, we get to apply a "multiplier" to our final
incentive payout calc to make the payout higher. It could be any percent but
if the result comes out to be:

95% then we get a 75% multiplier
98% then we get a 100% multiplier
100% then we get a 125% multiplier

Here's what I have so far:

ROUND(0.75+(IF(((H37-E37)/(G37-E37)/2)<0,0,IF(((H37-E37)/(G37-E37)/2)>0.5,0.5,(((H37-E37)/(G37-E37))/2-0.05)))),2)

It all works beautifully except when the result is 98%, I expect to see a
100% as the formulas result, except I get 105%.

H = 1
G = 1
E = .95

I know this is complicated and I hope I've explained it fully. Any help is
appreciated.

 

[CLR]

Maybe something like this.......

=LOOKUP(E37,{0,0.95,0.98,1},{0,0.75,1,1.25})


Vaya con Dios,
Chuck, CABGx3

 

[T. Valko]

It all works beautifully except...

Whenever you use the qualifier, "except", that means it doesn't work! <g>