SOLVED

Excel IF Formula

Copper 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 (Copper 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.

1 best response

Accepted Solutions
best response confirmed by ash_m200 (Copper 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.

View solution in original post