Calculating based on the better rate - per kg or pallet

Hi All,

I am trying to prepare the following freight calculator (please refer to the attachmed file):

Option 1) The table in blue - rates based on kg (in this case same rates from 0-3001+ kg):

way of calculating: if shipment is going to Adelaide = 490 kg, then 490*0.25+basic charge (9.48) = 131.98

Option 2) The table in yellow - rates are flat, if I ship on pallet (up to 500 kg only), then 490 kg to Adelaide = 103.12+13.56 = 116.68

The calculator should choose option 2, because it's cheaper (116.68 vs. 131.98).

If shipment is greater than 500 kg, then we should use the rates per kg.

Please help.

Thank you in advance.

Hi someko,

See attachement. And few comments:
- To effectively select one weight group it is better to have only lower or upper limit for the group (not the range 0-250) written in a cell. Then you can use formulas like VLOOKUP, HLOOKUP or INDEX...MATCH
- If data is real then there is no point to make such complicated table - rate per kg is flat in all cases.
- I made separate calculations for weight based and pallet based freight cost - of course it can be done as one long formula.
- I included in calculations also minimum freight column (you have not mentioned in your post but it was in the table).
- In a real life, the table with freight cost and actual calculations would be on separate sheets.
- I used the list-based data validation for cell with destination
- Note that choosen method cell (A16) is obtained by formula:

Please Login or Register  to view this content.

The main formula for blue table (B14) is:

Please Login or Register  to view this content.

and for the yellow one (B15):

Please Login or Register  to view this content.

PS. In your profile you have Excel 2003 while attached file was xlsx - so probably it is no longer 2003 Anyway, the formula works also on Ex2000 and probably earlier versions too.

Thank you Kaper, it's working pretty well.

I know this is an old post but I was wondering if someone could help me with a similar issue. I work for a transport company and need a formula that can calculate a weight pulled from a rate sheet. For example, the shipment is from point a to point b at 650 lbs. Rates for point b are min $50.00, ltl $15.00 per lb,500 lb rate $13.00 per lb, 1000 lb rate $12.00 per lb. I need a formula that will look at the rate sheet and give a rate whether it pulls the Min or 500 lb rate or cheaper as 1000 lbs. Any help would be appreciated. Thank you.