SOLVED
Home

Help with Complex Formula!

%3CLINGO-SUB%20id%3D%22lingo-sub-305887%22%20slang%3D%22en-US%22%3EHelp%20with%20Complex%20Formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-305887%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20calculate%20a%20fee%20amount%20with%20escalating%20bases%20for%20the%20calculation.%20For%20example%3A%3C%2FP%3E%3CP%3EBase%20amount%20up%20to%20%2425%2C000%20-%20fee%20is%2022.5%25%3C%2FP%3E%3CP%3EBase%20amount%20from%20%2425%2C001%20to%20%2475%2C000%20-%20fee%20is%2017.5%25%20(so%20this%20is%20on%20top%20of%20the%20fee%20for%20%2425%2C000)%3C%2FP%3E%3CP%3EBase%20amount%20in%20excess%20of%20%2475%2C001%20-%20fee%20is%2012.5%25%20(on%20top%20of%20the%20two%20previous%20level%20fees).%3C%2FP%3E%3CP%3EThere%20are%20approximately%20150%20base%20dollar%20amounts%20upon%20which%20I%20need%20to%20perform%20the%20calculation.%3C%2FP%3E%3CP%3EI%20know%20there%20is%20a%20formula%20that%20will%20do%20this%20quickly!%20HELP!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-305887%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-306088%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Complex%20Formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-306088%22%20slang%3D%22en-US%22%3EThank%20you%20Sergei!%20This%20is%20exactly%20what%20I%20needed.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-306016%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Complex%20Formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-306016%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Arul%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20that's%20not%20linear%20scheme.%20If%2C%20for%20example%2C%20your%20base%20is%20%2476000%20you%20pay%2022.5%25%20from%20first%20%2425000%2C%20plus%2017.5%25%20from%20(%2475000-%2425000)%2C%20plus%2012.5%25%20from%20(base-%2475000).%20Other%20words%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(A2%26lt%3B%3D25000%2CA2*0.225%2C25000*0.225%2BIF(A2%26lt%3B%3D75000%2C(A2-25000)*0.175%2C50000*0.175%2B(A2-75000)*0.125))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-306015%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Complex%20Formula!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-306015%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20the%20fix%20bases%20(A2)%20are%20those%203%2C%20the%20fee%20is%20calculated%20by%3A%3C%2FP%3E%3CP%3E%3DA2*(IF(A2%26gt%3B75000%3B12%2C5%25%3BIF(A2%26gt%3B25000%3B17%2C5%25%3B22%2C5%25))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20means%20that%20IF%20the%20amount%20is%20over%2075000%2C%20then%20apply%2012%2C5%25%3B%20if%20it's%20not%20(means%20it's%20less)%20then%20check%20if%20is%20more%20than%2025000%3B%20if%20it%20is%2C%20apply%2017%2C5%25%2C%20if%20it's%20less%20than%2025000%20then%20apply%2022%2C5%25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20all%20cases%2C%20you%20have%20to%20indent%20IF%20formulas%20(you%20can%20indent%20up%20to%207%20IFs%20in%20the%20same%20bracket)%20starting%20from%20the%20highest%20value%20you%20need%20to%20check.%20Check%20attachement!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted

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

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
Thank you Sergei! This is exactly what I needed.