SOLVED

Excel IF Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1642961%22%20slang%3D%22en-US%22%3EExcel%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1642961%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I've%20searched%20google%20and%20the%20forums%20for%20the%20answer%20to%20my%20query%20to%20no%20avail.%26nbsp%3B%20What%20I%20am%20trying%20to%20do%20is%20calculate%20a%20%25%20number%20based%20on%20a%20tiered%20structure%2C%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20B1%20will%20be%20a%20percentage%20based%20on%20the%20following%20tiered%20structure%2C%20where%20A1%20equals%20the%20dollar%20value%3A%3C%2FP%3E%3CP%3E%240-%24100%2C000%20%3D%200.00%25%3C%2FP%3E%3CP%3EThe%20next%20%24400%2C000%20%3D%200.05%25%3C%2FP%3E%3CP%3EThe%20next%20%24500%2C000%20%3D%200.10%25%3C%2FP%3E%3CP%3EAnything%20over%20%241%2C000%2C000%20%3D%200.20%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20get%20excel%20to%20give%20me%20the%20above%20percentages%20for%20set%20amounts%20in%20the%20brackets%20but%20it%20wont%20allow%20the%20tiered%20structure%20(e.g.%20%24550%2C000%20entered%20into%20A1%20is%20returning%200.10%25%20in%20B1%20whereas%20it%20should%20be%200.04%25).%26nbsp%3B%20I%20have%20this%20so%20far%3A%3C%2FP%3E%3CP%3E%3DIF(A1%26lt%3B100000%2C0%25%2CIF(AND(A1%26gt%3B100000%2CA1%26lt%3B500000)%2C0.05%25%2CIF(AND(A1%26gt%3B500000%2CA1%26lt%3B1000000)%2C0.1%25%2CIF(A1%26gt%3B1000000%2C0.2%25%2C0))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3EAshlea%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1642961%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643050%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-09-08_023922.png%22%20style%3D%22width%3A%20200px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216946i6362BAE40CD26EA5%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20title%3D%222020-09-08_023922.png%22%20alt%3D%222020-09-08_023922.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIn%20cell%20B1%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(A1%26gt%3BE4%3BF5%3BIF(A1%26gt%3BE3%3BF4%3BIF(A1%26gt%3BE2%3BF3%3BF2)))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20cannot%20use%20another%20cells%20like%20my%20picture%2C%20then%20replace%3A%3C%2FP%3E%3CP%3EE4%20to%20500%3C%2FP%3E%3CP%3EF5%20to%200%2C20%25%3C%2FP%3E%3CP%3EE3%20to%20400%3C%2FP%3E%3CP%3EF4%20to%200%2C10%25%3C%2FP%3E%3CP%3EE2%20to%20100%3C%2FP%3E%3CP%3EF3%20to%200%2C05%25%3C%2FP%3E%3CP%3EF2%20to%200%2C02%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20could%20help%20you.%3C%2FP%3E%3CP%3EBye.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643071%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643071%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F785197%22%20target%3D%22_blank%22%3E%40ash_m200%3C%2FA%3E%26nbsp%3BActually%2C%20similar%20questions%20have%20come%20up%20here%20twice%20the%20past%20week%2C%20though%20applied%20in%20different%20contexts.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fband-billing%2Fm-p%2F1632141%23M73901%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fband-billing%2Fm-p%2F1632141%23M73901%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Frequire-a-formula-for-excel-spreadsheet%2Fm-p%2F1624381%23M73665%22%20target%3D%22_self%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Frequire-a-formula-for-excel-spreadsheet%2Fm-p%2F1624381%23M73665%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20same%20type%20of%20solution%20that%20I%20provided%20then.%20I've%20adapted%20it%20to%20your%20specific%20situation.%20I%20trust%20you%20can%20apply%20the%20method%20demonstrated%20into%20your%20own%20file.%20If%20not%2C%20come%20back%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643139%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643139%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20-%20that%20did%20work%2C%20apologies%20for%20not%20finding%20the%20other%20posts%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643149%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643149%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F785197%22%20target%3D%22_blank%22%3E%40ash_m200%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EI%20believe%20this%20is%20best%20done%20by%20looking%20up%20the%20rate%20from%20a%20table.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSTRONG%3E%3D%20amount%20*%20LOOKUP(amount%2C%20rates)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216958iD57DD1E2FACD57DF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643157%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20IF%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F785197%22%20target%3D%22_blank%22%3E%40ash_m200%3C%2FA%3E%26nbsp%3BNo%20apologies%20needed.%20Not%20easy%20to%20find%20these%20threads%20where%20one%20calls%20it%20%22banded%20billing%22%20and%20another%20%22Help%20with%20a%20formula%22.%20You%20yourself%20called%20it%20%22Excel%20IF%20Formula%22.%20And%20all%20of%20you%20were%20looking%20for%20some%20kind%20of%20tiered%20fee%20calculation.%20Glad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, I've searched google and the forums for the answer to my query to no avail.  What I am trying to do is calculate a % number based on a tiered structure, as follows:

 

Cell B1 will be a percentage based on the following tiered structure, where A1 equals the dollar value:

$0-$100,000 = 0.00%

The next $400,000 = 0.05%

The next $500,000 = 0.10%

Anything over $1,000,000 = 0.20%

 

I can get excel to give me the above percentages for set amounts in the brackets but it wont allow the tiered structure (e.g. $550,000 entered into A1 is returning 0.10% in B1 whereas it should be 0.04%).  I have this so far:

=IF(A1<100000,0%,IF(AND(A1>100000,A1<500000),0.05%,IF(AND(A1>500000,A1<1000000),0.1%,IF(A1>1000000,0.2%,0))))

 

Thanks in advance,

Ashlea

9 Replies

2020-09-08_023922.png

In cell B1:

=IF(A1>E4;F5;IF(A1>E3;F4;IF(A1>E2;F3;F2)))

 

If you cannot use another cells like my picture, then replace:

E4 to 500

F5 to 0,20%

E3 to 400

F4 to 0,10%

E2 to 100

F3 to 0,05%

F2 to 0,02%

 

I hope this could help you.

Bye. 

Best Response confirmed by ash_m200 (New Contributor)
Solution

@ash_m200 Actually, similar questions have come up here twice the past week, though applied in different contexts.

https://techcommunity.microsoft.com/t5/excel/band-billing/m-p/1632141#M73901 

https://techcommunity.microsoft.com/t5/excel/require-a-formula-for-excel-spreadsheet/m-p/1624381#M73... 

 

Attached is the same type of solution that I provided then. I've adapted it to your specific situation. I trust you can apply the method demonstrated into your own file. If not, come back here.

Thank you - that did work, apologies for not finding the other posts

@Riny_van_Eekelen 

@ash_m200 

 

I believe this is best done by looking up the rate from a table.

 

= amount * LOOKUP(amount, rates)

 

image.png

@ash_m200 No apologies needed. Not easy to find these threads where one calls it "banded billing" and another "Help with a formula". You yourself called it "Excel IF Formula". And all of you were looking for some kind of tiered fee calculation. Glad I could help.

@Riny_van_Eekelen 

Why not

= SUMIFS(RateDiff, Lower, "<="&Qty)

You appear to be catering for very old versions of Excel.

@Peter Bartholomew True, sometimes I'm still a bit into the old way of doing things. A habit I'm trying to get over, though.

 

But in this case, I believe your formula doesn't give the right answer as the rate should be tiered. For instance, a quantity of 500000 should return a rate of 0.04% i.e. (100000 x 0% + 400000 x 0.05%)/ 500000.

Your formula picks-up the rate that is valid as from 500000. But perhaps I'm mistaken.

@Riny_van_Eekelen 

Please accept my apologies.  I had misread the original question and not realised it was a banded tax type of challenge.

 

I normally try to get the solution as a single array formula but, on this occasion, I have fallen back to using a Table to propagate a relative reference formula.  Maybe a transpose will sort it?

 

image.png

@Riny_van_Eekelen 

For the record, I did finally  manage to process an array of amounts with a single array formula spilling to provide the results but it is not for the faint-hearted!

= LET(
  BandedAmounts, DIFF( IF(amount<cap, amount, cap ), amount*0,,,,0),
  Payment,       SUMROWS( BandedAmounts * rates ),
  Payment )

It requires transposing the table of bounds and rates and using Charles Williams's FastExcel functions DIFF and SUMROWS to overcome Excels limitations in dealing with 2D arrays.

 

I would also observe that the solution looks nothing like a normal spreadsheet formula.