• 397K Members
• 3,695 Online
• 433K Conversations
SOLVED

New Contributor

# Help with Complex Formula!

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! :)

3 Replies

# Re: Help with Complex Formula!

If the fix bases (A2) are those 3, the fee is calculated by:

=A2*(IF(A2>75000;12,5%;IF(A2>25000;17,5%;22,5%))

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!

Solution

# Re: Help with Complex Formula!

Hi Arul,

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

`=IF(A2<=25000,A2*0.225,25000*0.225+IF(A2<=75000,(A2-25000)*0.175,50000*0.175+(A2-75000)*0.125))`

Highlighted

# Re: Help with Complex Formula!

Thank you Sergei! This is exactly what I needed.
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
203 Replies