Hi
I'm trying to create the following formula for a sliding retention calculation based on an invoice amount as follows:-
1. If the current amount invoiced is less than $200,000 retention value is 10%
2. If the current amount invoiced is between $200,000 and $800,000 the retention value is $200,000 @ 10% and the balance at 5%
3. If the current amount invoiced is over $800,000 the retention value is $200,000 @ 10% and $600,000 5% and the balance at 2%
For example
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
I'm trying to create the following formula for a sliding retention calculation based on an invoice amount as follows:-
1. If the current amount invoiced is less than $200,000 retention value is 10%
2. If the current amount invoiced is between $200,000 and $800,000 the retention value is $200,000 @ 10% and the balance at 5%
3. If the current amount invoiced is over $800,000 the retention value is $200,000 @ 10% and $600,000 5% and the balance at 2%
For example
<tbody> </tbody> | ||||||||||||||||||||||||||||||||||||||||||||||||
IF((C2)<=B4,C2*C4,IF(AND((C2)>B4,(C2)<=B5),(B4*C4)+(C2-B4)*C5,IF((C2)>B6,((B4*C4)+((C2-B4)*C5)+(C2-B6)*C6)))) Formula above is sort of there works for anything under $800,000 but over $800,000 doesn't give the right answer Any Help much appreciated Thanks |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>