Solved

Excel script / formula to calculate lowest shipping cost

Posted on 2014-04-21
12
1,023 Views
Last Modified: 2014-04-27
Hello Experts

I would like to add an Excel  function (either VBA or formulas) to check if the freight cost is cheaper if we use a higher assumed weight, rather than the actual weight. For example, in column L, the cost / kg is 1610 for weight <45 and 330 for >45. If actual weight is 43kg, it's cheaper to ship as 45kg.

Thanks!

Tom
Freight-Calculator-20140422.1.xlsx
0
Comment
Question by:tomfolinsbee
  • 8
  • 4
12 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 40014017
where you want to put formula?

Cell L12...?

where is mention the cap of weight.....>45   <45...?

where is weight?

Thanks
0
 

Author Comment

by:tomfolinsbee
ID: 40014030
Yes, formula would go in row 12.

Actual weight is in cell B10.

Row 13 is for the "shipping weight" -- could be the actual weight, or it could be higher weight.

The upper and lower bound of each weight group is in a22:B99.

Thanks for your interest.
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40014055
looking
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 8

Expert Comment

by:itjockey
ID: 40014148
See attached ...let me know if there is confusion..

Formula changed from VLOOKUP to INDEX  - MATCH in Row 11. it is up to you,  can revert back formula from your previous file.(in my attachment)

Formula for Cell L11=INDEX(L32:L95,(MATCH($B$10,$A$32:$A$95,1)))*$B$10
Formula For Cell L12=IF(L11>(ROUNDDOWN(INDEX($A$32:$A$95,MATCH($B$10,$A$32:$A$95,1)+1),0))*(INDEX(L32:L95,MATCH($B$10,$A$32:$A$95,1)+1)),(ROUNDDOWN(INDEX($A$32:$A$95,MATCH($B$10,$A$32:$A$95,1)+1),0))*(INDEX(L32:L95,MATCH($B$10,$A$32:$A$95,1)+1)),"")
Formula For Cell L13=IF(L12="","",ROUNDDOWN(INDEX($A$32:$A$95,MATCH($B$10,$A$32:$A$95,1)+1),0))

Copy across formula in rows.
See attached file
Thanks
Freight-Calculator-20140422.1.xlsx
0
 

Author Comment

by:tomfolinsbee
ID: 40014240
Thanks Itjockey.

I think solution needs to be able to check all the higher weight categories, not just the next  heavier category.  Solution works for actual weight 40-45kg since the next price break is >45kg.  However, if actual weight is between 30-40kg, it's still cheaper to ship as 45kg.

For example, 30kg actual weight and the quotation in Col L.  
30kg x 1610 = 49,200
45kg x 330 = 14,850

Perhaps need to use a script to loop through all the higher weight groups?
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40014393
got it...

Formula for Cell L12=SUMPRODUCT(MIN((INDIRECT("$A$"&MATCH($B$10,$A$1:$A$95,1)+1):$A$95)*((INDIRECT(ADDRESS((MATCH($B$10,$A$1:$A$95,1)+1),COLUMN()))):L95)))

L13 working on it.

Thanks
0
 
LVL 8

Accepted Solution

by:
itjockey earned 500 total points
ID: 40015408
i guess this attachment will solve your problem....
Freight-Calculator-20140422.1.xlsx
0
 

Author Comment

by:tomfolinsbee
ID: 40019177
itjockey, thanks for the update.  The formulas look really complicated so I added another section at row 133 where I calculate the $/package for all weight categories (previously it was mixture of $/package and $/kg).  Would this simply the formula for finding the $/package where

1) min weight (column A) is > than the actual weight; and
2) the $/package is cheaper than the $/package calculated with the actual weight?

Appreciate your help with this!
Freight-Calculator-20140424.3-Wo.xlsx
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40024883
Sorry For Delay In Reply ...sure on Monday or if i have time then Sunday
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40026561
1) min weight (column A) is > than the actual weight; and
2) the $/package is cheaper than the $/package calculated with the actual weight?

Where do u need formula to put?


Thanks
0
 
LVL 8

Expert Comment

by:itjockey
ID: 40026567
or I suggest close this question if your original post is satisfied & go for new question.

Thanks
0
 

Author Closing Comment

by:tomfolinsbee
ID: 40026608
I've made some changes to the model which will simplify the formula so will post a new question. Thank you!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question