Commission Formula

[tieva111]

Hello Everyone,

I have a question. Anything from $0 - $6.95 has 3% commission and anything above $14.25 has 7% commission. The amounts between $6.95 and $14.25 will get more than 3% but less than 7% (the commissions % should increase as they get closer to $14.25 with them capping out at $14.25 or 7%). How do I create a formula for this in excel so you type in a dollar number and it spits out the commission?

Thank you very much.
Scott

 

[tieva111]

I can get the part at or before $6.95 and at or after $14.25, but how do I capture the part in between the two numbers since the percentage increase after $6.95 but stops at 7% when it hits $14.25. Here is the formula I have so far: =IF(E4<=6.95,E4*0.03,IF(E4>=14.25,E4*0.07,0))

 

[sericom]

Try this, where A1 contains your dollar amount

=IF(AND(A1>=0,A1<=6.95),A1*0.03,IF(AND(A1>=6.95,A1<=14.25),(4*A1-5.9)/730*A1,0))

 

[tieva111]

Thank you so much. I had to add IF(A1>=14.25,A1*0.07,0))) to include the greater than $14.25 part, but thanks a million. I always try to figure out what the formula is doing. Is there anyway you can tell me how you got the (4*A1-5.9)/730*A1 part?

 

[Dave Patton]

Try with or without the If

Excel 2010
	A	B
1	5.00	0.15
2	10.00	0.47
3	20.00	1.40
1c

Cell Formulas
Range	Formula
B1	=IF(AND(A1>=0,A1<=6.95),A1*0.03,IF(AND(A1>=6.95,A1<=14.25),(0.03+(A1-6.95)/7.3*0.04)*A1,A1*0.07))
B2	=AND(A2>=0,A2<=6.95)*A2*0.03+AND(A2>=6.95,A2<=14.25)*(0.03+(A2-6.95)/7.3*0.04)*A2+(A2>14.25)*(A2*0.07)
B3	=AND(A3>=0,A3<=6.95)*A3*0.03+AND(A3>=6.95,A3<=14.25)*(0.03+(A3-6.95)/7.3*0.04)*A3+(A3>14.25)*(A3*0.07)