I need a formula to calculate a fee amount with escalating bases for the calculation. For example:
Base amount up to $25,000 - fee is 22.5%
Base amount from $25,001 to $75,000 - fee is 17.5% (so this is on top of the fee for $25,000)
Base amount in excess of $75,001 - fee is 12.5% (on top of the two previous level fees).
There are approximately 150 base dollar amounts upon which I need to perform the calculation.
I know there is a formula that will do this quickly! HELP! :)
View best response
If the fix bases (A2) are those 3, the fee is calculated by:
That means that IF the amount is over 75000, then apply 12,5%; if it's not (means it's less) then check if is more than 25000; if it is, apply 17,5%, if it's less than 25000 then apply 22,5%.
In all cases, you have to indent IF formulas (you can indent up to 7 IFs in the same bracket) starting from the highest value you need to check. Check attachement!
I guess that's not linear scheme. If, for example, your base is $76000 you pay 22.5% from first $25000, plus 17.5% from ($75000-$25000), plus 12.5% from (base-$75000). Other words like