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.

Maybe something like this.......

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


Vaya con Dios,
Chuck, CABGx3



"fgbdrum" wrote:

> 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.

>It all works beautifully except...

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

--
Biff
Microsoft Excel MVP


"fgbdrum" <(E-Mail Removed)> wrote in message
news:E6AC848D-4A4D-42AE-BF3A-(E-Mail Removed)...
> 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.