If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating shipping costs
Hello everyone. I'm a newbie here and have searched the New Users threads with no luck. I have a small sales business and have created a workbook to figure pricing. The problem I'm having is calculating shipping costs. We use UPS and they charge by the pound. I can total the weight of all items on an order but I can't figure out how to make the worksheet find the shipping costs based on the weight totals. For example, the freight weights a Weight, lbs Price Under 3 5.95 3 - 6.9 6.85 7 - 13.9 7.85 14 - 23.9 8.90 24 - 39.9 0.35 per lb 40 - 59.9 0.31 per lb and so on down to Over 1000 0.24 per lb. I've tried some of the VLOOKUP formulas in this forum without luck. I just don't know what I'm doing. Can someone help? Thank you very much, Lynn -- LPJR ------------------------------------------------------------------------ LPJR's Profile: http://www.excelforum.com/member.php...o&userid=29138 View this thread: http://www.excelforum.com/showthread...hreadid=488975 |
#2
|
|||
|
|||
Calculating shipping costs
Hi Lynn
The vlookup is your best option you just need to set your data up in the right format. Create a lookup table that looks like this: 0 5.95 3 6.85 7 7.85 14 8.9 etc Assuming this table is in the range A2:B20 and your weight is in cell D1 then the vlookup formula would be: =VLOOKUP(D1,$A$2:$B$20,2,1) See Debra Dalgleish's notes at: http://www.contextures.com/xlFunctions02.html#Range Hope this helps Rowan LPJR wrote: Hello everyone. I'm a newbie here and have searched the New Users threads with no luck. I have a small sales business and have created a workbook to figure pricing. The problem I'm having is calculating shipping costs. We use UPS and they charge by the pound. I can total the weight of all items on an order but I can't figure out how to make the worksheet find the shipping costs based on the weight totals. For example, the freight weights a Weight, lbs Price Under 3 5.95 3 - 6.9 6.85 7 - 13.9 7.85 14 - 23.9 8.90 24 - 39.9 0.35 per lb 40 - 59.9 0.31 per lb and so on down to Over 1000 0.24 per lb. I've tried some of the VLOOKUP formulas in this forum without luck. I just don't know what I'm doing. Can someone help? Thank you very much, Lynn |
#3
|
|||
|
|||
Calculating shipping costs
A straight VLOOKUP will work fine for weights 24 lbs. After that, the
shipping cost is proportional to the weight. One workaround could be =VLOOKUP(D1,$A$2:$B$20, 2, TRUE) * IF(D124, 1,D1) Note that if your package weighs 14 to 23.9 lbs, it's cheaper to add enough gravel to make the package 24 pounds. In article , Rowan Drummond wrote: Hi Lynn The vlookup is your best option you just need to set your data up in the right format. Create a lookup table that looks like this: 0 5.95 3 6.85 7 7.85 14 8.9 etc Assuming this table is in the range A2:B20 and your weight is in cell D1 then the vlookup formula would be: =VLOOKUP(D1,$A$2:$B$20,2,1) See Debra Dalgleish's notes at: http://www.contextures.com/xlFunctions02.html#Range Hope this helps Rowan LPJR wrote: Hello everyone. I'm a newbie here and have searched the New Users threads with no luck. I have a small sales business and have created a workbook to figure pricing. The problem I'm having is calculating shipping costs. We use UPS and they charge by the pound. I can total the weight of all items on an order but I can't figure out how to make the worksheet find the shipping costs based on the weight totals. For example, the freight weights a Weight, lbs Price Under 3 5.95 3 - 6.9 6.85 7 - 13.9 7.85 14 - 23.9 8.90 24 - 39.9 0.35 per lb 40 - 59.9 0.31 per lb and so on down to Over 1000 0.24 per lb. I've tried some of the VLOOKUP formulas in this forum without luck. I just don't know what I'm doing. Can someone help? Thank you very much, Lynn |
#4
|
|||
|
|||
Calculating shipping costs
Thanks, everyone. It works!!! JE, I entered your formula and then modified it to fit my worksheet parameters. The actual formula I wound up with is: =VLOOKUP(F305,$Q$315:$R$325, 2, TRUE) * IF(F30524, 1,F305) I had some trouble at first. Everything worked until the order weight went over 24 lbs. Then all I got for a result was the multiplier instead of the total shipping cost. I discovered that I entered 24 rather than 24 and changed for like you had in your formula and we're in business. Thank you both so much. I have been trying to do this on my own for so long I'm almost ashamed to admit it and I wasn't even close. This forum is awesome! Lynn -- LPJR ------------------------------------------------------------------------ LPJR's Profile: http://www.excelforum.com/member.php...o&userid=29138 View this thread: http://www.excelforum.com/showthread...hreadid=488975 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Simple Calculating Costs | Tanker350 | General Discussion | 1 | October 19th, 2005 03:26 AM |
shipping costs using if then | impression | Worksheet Functions | 2 | May 7th, 2005 11:34 PM |
Shipping address different from customer address | CC | New Users | 3 | March 30th, 2005 12:27 AM |
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | RICHARD | Worksheet Functions | 1 | March 15th, 2005 05:49 PM |
AGE CALCULATING EXCEL SPREADSHEETS AND OTHERS! | RICHARD | General Discussion | 0 | March 1st, 2005 01:53 PM |