Formula/Shipping Chart

[joaniemic]

I want to be able to say:
If E38 is less than or equal to $17.99 then E44 is E38 plus $5.95 and
If E38 is between $18.00 and $23.99 then E44 is E38 plus $6.95 and
If E38 is between $24.00 and $39.99 then E44 is E38 plus $7.95 and
If E38 is between $40.00 and $49.99 then E44 is E38 plus $8.95 and
If E38 is between $50.00 and $74.99 then E44 is E38 plus $10.95 and
If E38 is between $75.00 and $89.99 then E44 is E38 plus $12.95 and
If E38 is between $90.00 and $119.99 then E44 is E38 plus $14.95 and
If E38 is between $120.00 and $249.99 then E44 is E38 plus $16.95 and
If E38 is between $250.00 and $499.99 then E44 is E38 plus $18.95 and
If E38 is between $500.00 and $599.99 then E44 is E38 plus $21.95

(Shipping on E38 that is $600+ is on another scale, which I don't need in
this formula.) Thank you!

 

[Mike H.]

=E38+IF(E38<=17.99,5.95,IF(E38<=23.99,6.95,IF(E38<=39.99,7.95,IF(E38<=49.99,8.95,IF(E38<=74.99,10.95,IF(E38<=89.99,12.95,IF(E38<=119.99,14.95,IF(E38<=249.99,16.95,IF(E38<=499.99,18.95,IF(E38<=599.99,21.95,0))))))))))

 

[joaniemic]

Mike, This came up with an error message. Your formula does less than or
equal to for EVERY LINE, but I only have that in the FIRST LINE. The rest
are BETWEEN those 2 amounts.

 

[Mike H.]

An error must mean you didn't type it or copy it correctly because it worked
for me just fine.

And you don't understand the formula because the way an if,then,else works
is that if it finds a match on the first if statement then it stops,
otherwise it goes to the next one. And since we get to the second one, we
already know our number is greater than 17.99 so we can just evaluate on the
top limit of our range. It works just exactly as you asked it to work.

 

[Dave]

Hi,
If you're still having trouble, try this formula in E44
=VLOOKUP(E38,{0,5.95;18,6.95;24,7.95;40,8.95;50,10.95;75,12.95;90,14.95;125,16.95;250,18.95;500,21.95},2,1)+E38

If you need a warning for values 600 or over, try this
=IF(E38>=600,"Over $600. Use other
scale",VLOOKUP(E38,{0,5.95;18,6.95;24,7.95;40,8.95;50,10.95;75,12.95;90,14.95;125,16.95;250,18.95;500,21.95},2,1))+E38

The format of this window has wrapped the second formula, and may have added
an unwanted invisible character, which would need to be removed.
Regards - Dave.