Multiple IF(AND) Statements

[kevin_evan]

I am attempting to calculate a commission based on the range that the number falls in between. I keep getting a #value error.

C1= $15
C2= $1,000,000
C5= $0.00
D5= .75%
B6= $.01
C6= $15.00
D6= 2.00%
B7= $15.01
C7 = $50.00
D7 = 2.50%
B8= $50.01
D8= 3.00%
This is the formula that I have
=IF(C1<=C5,C2*D5),IF(AND(C1>=B6,C6<=C1),C2*D6),IF(AND(C1>=B7,C1<=C7),D7*C2),IF(C1>=B8,D8*C2,0)

[Pepe Le Mokko]

Hi
it is not quite clear what you are trying to achieve
Perhaps make and post a sheet with three columns : lower boundary, upper boundary and commission

[abubaniyan]

Do you want to add all this commisson values

[kevin_evan]

IF AND Calculation 5-7-13.xlsx Here is the spreadsheet. What I am trying to do is wherever the number C1 falls in the 4 different ranges, then I need to calculate the percentage based on that range. So in this case the result should be $1,000,000* 2.00%

[Melvinrobb]

See the attached file. Does this work?

[abubaniyan]

try this...!

=IF(C1<=C5,C2*D5,IF(AND(C1>=B6,C6>=C1),C2*D6,IF(AND(C1>=B7,C1<=C7),D7*C2,D8*C2)))

[kevin_evan]

Thank you both so much. They both work!