Improve company productivity with a Business Account.Sign Up

x
Solved

# Excel script / formula to calculate lowest shipping cost

Posted on 2014-04-21
Medium Priority
1,325 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
Question by:tomfolinsbee
• 8
• 4
12 Comments

LVL 8

Expert Comment

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

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

ID: 40014055
looking
0

LVL 8

Expert Comment

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

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

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

Naresh Patel earned 2000 total points
ID: 40015408
i guess this attachment will solve your problem....
Freight-Calculator-20140422.1.xlsx
0

Author Comment

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

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

LVL 8

Expert Comment

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

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

Thanks
0

Author Closing Comment

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month8 days, 5 hours left to enroll

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

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