Captain Hindsight
New Member
- Joined
- Oct 9, 2013
- Messages
- 46
I am struggling to put a formula together for the below set of inputs:
[TABLE="width: 155"]
<tbody>[TR]
[TD]Target achievement[/TD]
[TD]% payout[/TD]
[/TR]
[TR]
[TD="align: right"]90%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]95%[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]110%[/TD]
[TD="align: right"]150%[/TD]
[/TR]
[TR]
[TD]120%[/TD]
[TD="align: right"]200%[/TD]
[/TR]
</tbody>[/TABLE]
So if the target was £1000, and they achieved £900 they get nothing, if they got £1100 they get 150%.
I need to know the payout % based on actual achievement. Note how the rate the payout increases is double when above 100%.
Here's the formula I have which just returns false.
=IF((N7>=T8),"200%",IF((N7<=$R$8),"0",IF((N7<=R8<=S8),((O7-V7)*10),IF((N7<=S8<=T8),(V9+((O7-V9)*5))))))
Where N7 is the value achieved, M7 is the target. R6 to T6 are the values to achieve 0%, 100% or 200% payout.
V7 to V11 is the target achievement %'s and W7 to W11 is the payout %'s.
[TABLE="width: 155"]
<tbody>[TR]
[TD]Target achievement[/TD]
[TD]% payout[/TD]
[/TR]
[TR]
[TD="align: right"]90%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]95%[/TD]
[TD="align: right"]50%[/TD]
[/TR]
[TR]
[TD="align: right"]100%[/TD]
[TD="align: right"]100%[/TD]
[/TR]
[TR]
[TD="align: right"]110%[/TD]
[TD="align: right"]150%[/TD]
[/TR]
[TR]
[TD]120%[/TD]
[TD="align: right"]200%[/TD]
[/TR]
</tbody>[/TABLE]
So if the target was £1000, and they achieved £900 they get nothing, if they got £1100 they get 150%.
I need to know the payout % based on actual achievement. Note how the rate the payout increases is double when above 100%.
Here's the formula I have which just returns false.
=IF((N7>=T8),"200%",IF((N7<=$R$8),"0",IF((N7<=R8<=S8),((O7-V7)*10),IF((N7<=S8<=T8),(V9+((O7-V9)*5))))))
Where N7 is the value achieved, M7 is the target. R6 to T6 are the values to achieve 0%, 100% or 200% payout.
V7 to V11 is the target achievement %'s and W7 to W11 is the payout %'s.