+ Reply to Thread
Results 1 to 14 of 14

Freight cost calculation based on weight and distance

  1. #1
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Red face 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
    Attached Files Attached Files

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,006

    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: copy to clipboard
    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?
    Click the * to say thanks.

  3. #3
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    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...

  4. #4
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Re: Freight cost calculation based on weight and distance

    @PaulM100: Can you also please share your excel workbook....

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    2,006

    Re: Freight cost calculation based on weight and distance

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

    And for E column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,843

    Re: Freight cost calculation based on weight and distance

    With 2 (hidden) help columns to make the formulas shorter
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Re: Freight cost calculation based on weight and distance

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

  8. #8
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    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. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    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?
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  10. #10
    Valued Forum Contributor
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016 / O365
    Posts
    440

    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.
    Attached Files Attached Files
    My English is very poor, so please be patient >_<"

  11. #11
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Freight cost calculation based on weight and distance

    Administrative Note:

    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. #12
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    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

    https://chandoo.org/forum/threads/fr...istance.42776/

    Thanks,
    Arun

  13. #13
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Re: Freight cost calculation based on weight and distance

    Hi Menem,

    Appreciate your help!..

    Thanks,
    Arun

  14. #14
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    2010
    Posts
    110

    Re: Freight cost calculation based on weight and distance

    Hi Richard,

    Appreciate your help!!...

    Thanks,
    Arun

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Freight Table - weight and zone lookup, return cost
    By yaman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-04-2014, 02:25 PM
  2. How to calculate transport cost from distance & weight slab
    By linardni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 12:02 PM
  3. Freight Calculate per distance
    By mohammedalkhatim in forum Excel Formulas & Functions
    Replies: 21
    Last Post: 11-06-2013, 06:30 AM
  4. FREIGHT CALCULATOR - Having issue with MAX / Multiplier Weight
    By icmpl in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-31-2013, 11:17 AM
  5. Freight calculator based on zipcode, weight and type
    By Verox in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 04-29-2013, 05:33 AM
  6. match, sum based on max. freight based on furthest distance formula.
    By simpson in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-30-2012, 02:01 PM
  7. Replies: 1
    Last Post: 04-30-2012, 02:32 PM
  8. Cost of Carriage based on Volumetric Weight
    By raylogic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2007, 03:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1