If than statement with multiple values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
Would I be able to create a if than statement for the following example.

If total is 0-99 than add 15
if total is 100-2000 than add 10%
if total is 2001-5000 than add 7%
if total is 5001-15000 than add 5%
if total is over 15000 than add 4%

Thanks!
 
Maybe this:

=IF(A1<=99,A1+(A1*0.15),IF(A1<=2000,A1+(A1*0.1),IF(A1<=5000,A1+(A1*0.7),IF(A1<=15000,A1+(A1*0.5),A1+(A1*0.4)))))

HTH,
Paul
 
Hi,

Try this:

=A1*IF(AND(A1>0,A1<100),1.15,IF(A1<2001,1.1,IF(A1<5001,1.07,IF(A1<15001,1.05,1.04))))

Thanks,
 
=IF(A1>15000,1.04,IF(A1>5000,1.05,IF(A1>2000,1.07,IF(A1>100,1.1,1.15))))*A1
You could replace A1 by, for example, SUM(B2:B20)
best wishes
 
try
=A1+LOOKUP(A1,{0,100,2001,5001,15001},{0.15,0.1,0.07,0.05,0.04})*A1
or =A1*(1.04+LOOKUP(A1,{0,100,2001,5001},{0.11,0.06,0.03,0.01}))
 
One method...........

=LOOKUP(B1,{0,100,2001,5001,15000},{1.15,1.1,1.07,1.05,1.04})*B1


Gord Dibben MS Excel MVP
 
Back
Top