Multi-function formula (=IF)

[Suzimc78]

I would like to write a formula for shipping cost in Excel 2003, for specific shipping weights. Each product is a different weight. The result is in a new column.
What formula would I use for this column? The figures below are hypothetical.

A1 is 4 lbs. Shipping is $6.00
A2 is 8 lbs. Shipping is $12.90
A3 is 10 lbs. Shipping is $16.00
A4 is 15 lbs. Shipping is $24.00


Formula for the column is ?

If A1 is greater than 1 lb. and less than 5 lbs. then shipping cost is $6.00 or
A1 is greater than 7 lbs. and less than 10 lbs. then shipping cost is 12.90 or
A1 is greater than 9 lbs. and less than 15 lbs.then shipping cost is $16.00
A1 is greater than 14 lbs. and less than 18 lbs. then shipping cost is $24.50

[JBeaucaire]

Try this:
=LOOKUP(A1, {0,1,7,9,14}, {0,6,12.9,16,24.5})

[JBeaucaire]

If your shipping rates might change, you could put those values into a table and reference them as cell ranges.

Please Login or Register  to view this content.

The the formula would change to:
=LOOKUP(A1, $G$2:$G$5, $H$2:$H5)

For these lookup tables, all we're setting in the first range is the starting point for each shipping rate

[Suzimc78]

Works Great!!

Thank you for taking the time to reply so quickly.
=LOOKUP(G1, {0,1,3.01,5.01,8.01,11.01}, {0,11,13.9,16.9,22,50})
This is the final formula I used.

Thank you,

Susan and Michael McDonald
http://www.green-living-healthy-home.com:)

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)