Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel formula help for calculating freight.
Hello everyone,
I need help with a formula please. Spreadsheet is attached in zip file. The attached spreadsheet has (4) sheets. The first sheet contains the formulas, the 2nd through 4th sheets contain freight costs/weights for 3 different freight companies. Columns H, I, and L already have active formulas within them. Column M is where I need the freight formula. ------------ This is what I would like to have happen (if possible) in Column M under Shipping Cost but can't seem to figure it out. Here are my scenarios: 1. If Column H and Column I show "Eligible" or if Column H shows "Eligible" and Column I shows "NonEligible", use data from ShipMethod1 Freight sheet and depending on what Column L is showing as the "Calculated Weight" value, pull the corresponding freight cost from ShipMethod1 Freight sheet into Column M. (Note: The issue here is that Calculated Weight is not always a whole number so the calculation has to look at weight ranges on the freight table to choose the right associated cost. So for example, if the Calculated Weight was .35, the formula should choose the freight cost value of $2.09 from ShipMethod1 Freight sheet.) 2. If Column H shows "NonEligible" and Column I shows "Eligible", use data from ShipMethod2 Freight sheet and depending on what Column L is showing as the "Calculated Weight" value, pull the corresponding freight cost from ShipMethod2 Freight sheet into Column M. (Note: Same issue here with ranges...) 3. If Column H shows "NonEligible" and Column I shows "NonEligible", use data from ShipMethod3 Freight sheet and depending on what Column L is showing as the "Calculated Weight" value, pull the corresponding freight cost from ShipMethod3 Freight sheet into Column M. (Note: Same issue here with ranges...) ------------ I hope this makes sense... Thank you for any and all help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula help for calculating freight.
Hi Roland,
Am Thu, 1 Aug 2013 20:29:51 +0100 schrieb roland_arv: 1. If Column H and Column I show "Eligible" or if Column H shows "Eligible" and Column I shows "NonEligible", use data from ShipMethod1 Freight sheet and depending on what Column L is showing as the "Calculated Weight" value, pull the corresponding freight cost from ShipMethod1 Freight sheet into Column M. (Note: The issue here is that Calculated Weight is not always a whole number so the calculation has to look at weight ranges on the freight table to choose the right associated cost. So for example, if the Calculated Weight was .35, the formula should choose the freight cost value of $2.09 from ShipMethod1 Freight sheet.) 2. If Column H shows "NonEligible" and Column I shows "Eligible", use data from ShipMethod2 Freight sheet and depending on what Column L is showing as the "Calculated Weight" value, pull the corresponding freight cost from ShipMethod2 Freight sheet into Column M. (Note: Same issue here with ranges...) 3. If Column H shows "NonEligible" and Column I shows "NonEligible", use data from ShipMethod3 Freight sheet and depending on what Column L is showing as the "Calculated Weight" value, pull the corresponding freight cost from ShipMethod3 Freight sheet into Column M. (Note: Same issue here with ranges...) try in M2: =INDEX('ShipMethod1 Freight'!B2:B151,MATCH(L2,'ShipMethod1 Freight'!A2:A151,1))*(H2="Eligible")+INDEX('ShipMe thod2 Freight'!B2:B151,MATCH(L2,'ShipMethod2 Freight'!A2:A151,1))*(AND(H2="NonEligible",I2="Eli gible"))+INDEX('ShipMethod3 Freight'!B2:B151,MATCH(L2,'ShipMethod3 Freight'!A2:A151,1))*(AND(H2="NonEligible",I2="Non Eligible")) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Hello Claus,
Thank you so much for looking at this. Unfortunately, it did not work for us. We actually did a work-around that seems to be working at this time. Thank you again. Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an Excel Freight Calculator | Excel Worksheet Functions | |||
freight calculator | Excel Discussion (Misc queries) | |||
Freight Calculation | Excel Programming | |||
Freight formula | Excel Discussion (Misc queries) | |||
how to analyze freight expense? | Excel Worksheet Functions |