If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complicated Forumla Help
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. |
#2
|
|||
|
|||
Complicated Forumla Help
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. |
#3
|
|||
|
|||
Complicated Forumla Help
It all works beautifully except...
Whenever you use the qualifier, "except", that means it doesn't work! g -- Biff Microsoft Excel MVP "fgbdrum" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|