Captain Hindsight
New Member
- Joined
- Oct 9, 2013
- Messages
- 46
I am struggling to put a formula together for the below set of inputs:
<tbody>
</tbody>
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.
| Target achievement | % payout |
| 90% | 0 |
| 95% | 50% |
| 100% | 100% |
| 110% | 150% |
| 120% | 200% |
<tbody>
</tbody>
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.
