09-07-2020 10:09 PM
09-07-2020 10:09 PM
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:
Thanks in advance,
09-07-2020 10:46 PM
In cell B1:
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.
09-07-2020 10:52 PM - edited 09-07-2020 10:59 PMSolution
@ash_m200 Actually, similar questions have come up here twice the past week, though applied in different contexts.
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.
09-07-2020 11:30 PM - edited 09-07-2020 11:32 PM
@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.
09-08-2020 01:17 AM
@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.
09-08-2020 03:07 AM
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?
09-08-2020 03:52 AM
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.