Excel Problem regarding Formula

[jonathanhili94]

Hi I am trying to write the below in a formula but I am having some problems, I tried to a formula but is not working.

You write amount (and on which category the amount fall it will work the sum)

If amount is between 0.00 to 9100.00 = 0% - 0.00
If amount is between 9101.00 to 14500.00 = 15% - 1365
If amount is between 14501.00 to 19500.00 = 25% - 2815
If amount is between 19501.00 to 60000.00 = 25% - 2725
If amount is between 60001.00 and over = 35% - 8725

- mean minus.

 

[Joe4]

Welcome to the Board!

You can do a nested IF statement. If the amount is in cell A1, it would look something like this:

=IF(A1>60000,(A1*0.35)-8725,IF(A1>19500,(A1*0.25)-2725,IF(A1>14500,(A1*0.25)-2815,IF(A1>9100,(A1*0.15)-1365,0))))

 

[hrayani]

Hi,

Is this what you want

=IF(AND(A1>=0,A1<=9100),0,IF(AND(A1>=9101,A1<=14500),A1*0.15-1365,IF(AND(A1>=14501,A1<=19500),A1*0.25-2815,IF(AND(A1>=19501,A1<=60000),A1*0.25-2725,IF(A1>60000,A1*0.35-8725)))))

 

[Joe4]

Hi,

Is this what you want

=IF(AND(A1>=0,A1<=9100),0,IF(AND(A1>=9101,A1<=14500),A1*0.15-1365,IF(AND(A1>=14501,A1<=19500),A1*0.25-2815,IF(AND(A1>=19501,A1<=60000),A1*0.25-2725,IF(A1>60000,A1*0.35-8725)))))

Using all the ANDs is totally unnecessary, if you understand how nested IFs work. The key part is once it hits a TRUE statement, it stops there, and will not evaluate the rest of the statement.
So you can either work from the top down like I did, i.e.
1st If: If the value is greater than the largest limit, then do something.
2nd If: If the value is greater than the second largest limit, then do something,
etc.

Alternatively, you could go in the reverse direction, and work from the bottom up:
1st If: If the value is less than the smallest limit, then do something.
2nd If: If the value is less than the second smallest limit, then do something,
etc.

If you follow either of those patterns, there is no need to use any ANDs in your formula (see my formula above in the previous reply).

 

[hrayani]

The key part is once it hits a TRUE statement, it stops there, and will not evaluate the rest of the statement.

Thanks Joe,

I did not know that. New learning for me

Regards,

 

[Joe4]

Thanks Joe,

I did not know that. New learning for me

You are welcome.

That is one of the great things about being on this board!
I often find myself learning new things too when I am helping others!

 

[MrD31]

Hi Jon.

I think I have been beaten to it to provide a solution for you, but I'll leave it here anyway:

=IF(A1 <= 9100,SUM((0/100)-0), IF(A1 <= 14500,SUM((15/100) - 1365),IF(A1 <= 19500,SUM((25/100) - 2815),IF(A1 <= 60000,SUM((25/100) - 2725),IF(A1 > 60000,SUM((35/100) - 8725))))))

 

[Toadstool]

Hi Jonathanhili94,

Here's my solution with a different approach:

Jonathanhili94.xlsx
	A	B
1	0	0
2	9000	0
3	12000	435
4	16677	1354.25
5	23000	3025
6	66999	14724.65
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=CHOOSE(MATCH(A1,{0,9101,14501,19501,60001},1),A1*0%-0,A1*15%-1365,A1*25%-2815,A1*25%-2725,A1*35%-8725)

 

[Joe4]

Hi Jon.

I think I have been beaten to it to provide a solution for you, but I'll leave it here anyway:

=IF(A1 <= 9100,SUM((0/100)-0), IF(A1 <= 14500,SUM((15/100) - 1365),IF(A1 <= 19500,SUM((25/100) - 2815),IF(A1 <= 60000,SUM((25/100) - 2725),IF(A1 > 60000,SUM((35/100) - 8725))))))

The SUM function serves no purpose in your formula.
I probably see this function misused more than any other function in Excel.

SUM is to be used when adding a range of values. Like if you wanted to add the values in cells A1:M1, you would use:
=SUM(A1:M1)

I also see people who want to add two values and do something like:
=SUM(A1+B1)

That is redundant and unnecessary. Just use + or SUM, but not both.
So it would be:
=A1+B1
or
=SUM(A1:B1)

You can totally remove all the sum references from your formula.

But you also have an error/omission from your formula. You forgot to multiply your percentages by A1.
I am fairly certain that the percentages the OP listed are the percentages of the original value. Otherwise, it does not make much sense.