Forum Discussion

rjacobsen's avatar
rjacobsen
Copper Contributor
Dec 21, 2018
Solved

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 th...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Dec 22, 2018

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

     

Arul Tresoldi's avatar
Arul Tresoldi
Iron Contributor
Dec 22, 2018

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!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 22, 2018

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

     

    • rjacobsen's avatar
      rjacobsen
      Copper Contributor
      Dec 23, 2018
      Thank you Sergei! This is exactly what I needed.

Resources