[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1204
  • Last Modified:

Excel script / formula to calculate lowest shipping cost

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
tomfolinsbee
Asked:
tomfolinsbee
  • 8
  • 4
1 Solution
 
Naresh PatelTraderCommented:
where you want to put formula?

Cell L12...?

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

where is weight?

Thanks
0
 
tomfolinsbeeAuthor Commented:
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
 
Naresh PatelTraderCommented:
looking
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Naresh PatelTraderCommented:
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
 
tomfolinsbeeAuthor Commented:
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
 
Naresh PatelTraderCommented:
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
 
Naresh PatelTraderCommented:
i guess this attachment will solve your problem....
Freight-Calculator-20140422.1.xlsx
0
 
tomfolinsbeeAuthor Commented:
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
 
Naresh PatelTraderCommented:
Sorry For Delay In Reply ...sure on Monday or if i have time then Sunday
0
 
Naresh PatelTraderCommented:
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
 
Naresh PatelTraderCommented:
or I suggest close this question if your original post is satisfied & go for new question.

Thanks
0
 
tomfolinsbeeAuthor Commented:
I've made some changes to the model which will simplify the formula so will post a new question. Thank you!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now