Forum Discussion
Excel IF Formula
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
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
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.
9 Replies
I believe this is best done by looking up the rate from a table.
= amount * LOOKUP(amount, rates)
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
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
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.
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.
