Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add forumla | Excel Discussion (Misc queries) | |||
Forumla | Excel Discussion (Misc queries) | |||
Forumla Help | Excel Discussion (Misc queries) | |||
Forumla Help | Excel Discussion (Misc queries) | |||
Forumla | Excel Worksheet Functions |