# Freight cost calculation based on weight and distance

1. ## Freight cost calculation based on weight and distance

Hi All,

Hope you are doing well!...I am trying to find the freight cost that will be incurred based on the weight that is to be carried and the miles to travel to reach the destination for ground service and also the fuel charge incurred ... Can you please help me here....I have provided the required input and output in the sheet attached....

In my attached sheet following are the tabs:

Carrying cost: This tab has the Miles ranges and the weight in pounds...the cost is to be multiplied based on the weight to be carried multiplied by the number got out of the table for ex: if 100 pounds are to be carried for 75miles then the cost is 1.56*100 =156\$ without fuel surcharge..

Fuel surcharge: This table represent the percentage of the cost that is to be added as fuel surcharge: ,For example if 300 pounds are to be carried for 500 miles then the cost would be 2.09*300=627 ...Adding the fuel surcharge it would be an addition of 12% cost based on the fuel surcharge table : ie: 627+0.12*627=702.24

Input& Required Output:The order id (oid), weight in pounds to be carried and the distance in miles are the inputs ..The output is the cost without fuel surcharge and the cost with fuel surcharge....

Thanks,
Arun

2. ## Re: Freight cost calculation based on weight and distance

For the first formula, I made some changes in carrying tab, meaning that I added the actual miles no in column A and B(min and max) and the actual pound in row 1 and 2 and becomes:
Formula:
`Please Login or Register  to view this content.`

For your second formula you don't have a common key. How it should know what surcharges applies? based on the cost from carrying cost tab?

3. ## Re: Freight cost calculation based on weight and distance

@PaulM100 : Glad to hear from you!... Appreciate your reply!..Yes for the second formula the cost from the carrying cost tab is the key...

5. ## Re: Freight cost calculation based on weight and distance

The formula provided is wrong. Change to:
Formula:
`Please Login or Register  to view this content.`

And for E column:
Formula:
`Please Login or Register  to view this content.`

6. ## Re: Freight cost calculation based on weight and distance

With 2 (hidden) help columns to make the formulas shorter

7. ## Re: Freight cost calculation based on weight and distance

@popipipo : Really appreciate your response!..Thank you very much!!...

8. ## Re: Freight cost calculation based on weight and distance

@PaulM100: I am still having some issues with the results of the formula as it is not the same as I had shared in the sample workbook...

9. ## Re: Freight cost calculation based on weight and distance

See attached.

But as Paul said way back in #2 what's the key for establishing which value from the fuel surcharge rates to use?
e.g. in E3 you are using 16%, presumably from F15 on the fuel surcharge sheet.
Presumably the Fuel Rate values in column B are relevant, but from your basic input, i.e. Mileage and weight how do you calculate a rate between 2.45 & 2.49 in order to identify row 15?

10. ## Re: Freight cost calculation based on weight and distance

I try to rebuild your master tables (carry_cost, Fuel_charge) and re-design to add some missing elements in the calculation sheet.
(All are in green tabs)

Basically use Index & Match for matching weight & distance , vlookup for find fuel charge.

Regards.

11. ## Re: Freight cost calculation based on weight and distance

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

12. ## Re: Freight cost calculation based on weight and distance

Hi pepe Le Mokko,

My Apologies!..I will make sure this does not happen again!...Following are the cross links...

https://www.excelguru.ca/forums/show...2340#post42340

Thanks,
Arun

Hi Menem,

Thanks,
Arun

14. ## Re: Freight cost calculation based on weight and distance

Hi Richard,

Thanks,
Arun

There are currently 1 users browsing this thread. (0 members and 1 guests)