Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Shipping Chart
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Shipping Chart
=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,2 1.95,0))))))))))
"joaniemic" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Shipping Chart
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." wrote: =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,2 1.95,0)))))))))) "joaniemic" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Shipping Chart
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. "joaniemic" wrote: 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." wrote: =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,2 1.95,0)))))))))) "joaniemic" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/Shipping Chart
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.9 5},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;50 0,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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a formula that calculates shipping costs? | New Users to Excel | |||
How do I create a formula that calculates shipping costs? | New Users to Excel | |||
Shipping price, maybe challenge of the day | Excel Worksheet Functions | |||
shipping costs using if then | Excel Worksheet Functions | |||
Multiple Shipping problem | Excel Worksheet Functions |