asked on

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

Naresh Patel

where you want to put formula?

Cell L12...?

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

where is weight?

Thanks

tomfolinsbee

ASKER

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.

Naresh Patel

looking

Naresh Patel

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

tomfolinsbee

ASKER

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?

Naresh Patel

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

ASKER CERTIFIED SOLUTION

Naresh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tomfolinsbee

ASKER

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

Naresh Patel

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

Naresh Patel

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

Naresh Patel

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

Thanks

tomfolinsbee

ASKER

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