+ Reply to Thread
Results 1 to 22 of 22

Freight Calculate per distance

  1. #1
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Freight Calculate per distance

    Hello Everyone,

    I've made some effort to obtain the distance value and calculate the total freight cost according to the destination of that cargo.

    My main problem is that, some parts were unavailable when I change from one to another.

    Could anyone support me in getting all this locations matched together?
    Attached Files Attached Files

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    Not exactly sure what you want here, But it looks like you are missing your unit cost value from the total calculation - ie:
    Total = cost per unit per kilometer * units * kilometers
    If you could show what the value for a set of ports and units appears as, and then what it should appear as, we would have a better chance of resolving this
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Contributor
    Join Date
    04-21-2007
    Location
    Lima, Peru
    MS-Off Ver
    2000, 2007, 2010
    Posts
    674

    Re: Freight Calculate per distance

    Hi

    You are using match with the RIGHT function to return the last two letters, but on the sheet there are both two and three letter combinations, please find attached a sample that uses only the name.

    Regards

    Jeff
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Quote Originally Posted by dredwolf View Post
    Not exactly sure what you want here, But it looks like you are missing your unit cost value from the total calculation - ie:
    Total = cost per unit per kilometer * units * kilometers
    If you could show what the value for a set of ports and units appears as, and then what it should appear as, we would have a better chance of resolving this
    Hi dredwolf,
    You're right, and i wanted to apply the following
    1. The unit cost is 50 USD (Fixed).
    2. The Rate per unit per kilometer is 0.16 USD
    the formula has to do the calculation using this method, Please try to make it for me.

    Many thanks,

  5. #5
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Hi Jeff,

    I realy appreciated what you made, it works fine with me

    Thanks,
    Moh

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    so, just so we are both working on the same page here, Basic per unit cost is $50 (USD) per Ton, And add 0.16 (USD) per kilometer per Ton ?

    Edit -

    Also, should Tons be rounded up if partial ?

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    If last post is in agreement, then this in G6 of Jeff's updated workbook should work for you :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    should give you the right number (Note - this does not round up partial tons)

    EDIT 2-
    Also, just a suggestion, you may want to make the rate per kilometer a cell reference, it will make adjusting the rate/ per kilometer easier to adjust, as it is now, you have to adjust the formula in F6 to change it
    Last edited by dredwolf; 10-27-2013 at 01:18 AM.

  8. #8
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Quote Originally Posted by dredwolf View Post
    If last post is in agreement, then this in G6 of Jeff's updated workbook should work for you :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    should give you the right number (Note - this does not round up partial tons)
    HI my Friend,

    it seems okay and works fine.

    Check the attached after the amendment you just suggested to me and see if i made a mistake.
    Attached Files Attached Files

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    If this is third post of this, I apologize, something wrong with the servers I think, as every other site I visit works Fine
    The numbers look right, is the total what you would expect?
    Also look at the edit in post #7, it may make life easier in the future

  10. #10
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Wink Re: Freight Calculate per distance

    Hello Everyone,
    I would like to have a drop down menu listing the following two choices:
    1. tonnage.
    2. volumetric

    I've attached a book that, consist of two sheet the 1st sheet containing the method of calculation the volumetric (the user has to fill the length and width and height) then will automatically generate the weight according to predefined equation (the same i made in the second sheet "Break bulk" ) in which will calculate weight/ton.

    I 'm currently looking forward to maximize my effort to do the following:
    merge the two sheets in single sheet and make a drop down list as per above requirement in order to do the following:
    - if the user has selected tonnage from the drop down menu and filled the quantity of that , then it has to calculate the entered amount of weight"tonnage".
    - if the user has selected volumetric from the drop down menu then it has to generate automatically three cells additional with the following:
    1. First Cell for length
    2. Second Cell for width
    3. Third Cell for height
    i have also made a formulas to obtain the above three values and execute the equation then take the result "in which would be the equivalent of tonnage and complete the rest of calculations.

    Does anybody can support me on doing that and make a valuable input for attractive view, would highly appreciated.



    Thanks very much.
    Attached Files Attached Files

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    I hope you can live with some VBA, as I don't see away around this without at least a little bit of it
    (I can with formulas hide the volumetric parts of the sheet, and make it so you cannot type in those cells (with data validation), but the area where the tonnage is expected needs to either have a formula, or an entered value, so that means using VBA to either, put a formula there, or remove the formula)
    Also, just a question, if the party wants to use a combination of both (ie: first shipment volumetric, second shipment tonnage) is that something you want to allow for? or you would use separate sheets for that?
    Last edited by dredwolf; 10-31-2013 at 07:53 PM. Reason: fixing some spelling errors :)

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    Without using VBA, see if attached is something you can work with, to get much better, we are going to need to go to a VBA solution I believe
    A10 on the first sheet contains the drop down to select Tonnage or Volumetric calculation,
    The new/changed formulas are too numerous to mention, but a lot of it is Conditional Formatting, and Data Validation formulas to show/hide things, or allow/not allow data entry into cells (please remember that Data Validation will Not stop copy pasting into the cells), most of the rest is simple IF(... ) statements to display or not display information

    Hope this helps

    EDIT- tried to upload the file, but I keep getting rejected, not sure whats going on here...
    Last edited by dredwolf; 10-31-2013 at 09:25 PM. Reason: Forgot File

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    I think it worked this time...
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Quote Originally Posted by dredwolf View Post
    I think it worked this time...
    Dredwolf,

    It works fine ya, i agree with you about the party wants to ship combination of cargo .e.g. "container + 50/ton soap" then we need to use both formulas, and sometimes the two formulas 3 times mean 3 different shipment.
    i also belies in VBA and it's okay, just to keep you on development i also intend to do the following:
    1. currency converter from USD to SSP with ability to modify the current rate in which 4.3 SSP as of today.
    2. From A18-J18 i will place an invoice template in which obtain the same results of calculation and data entered to the description and amount.
    For Example:
    Route
    From To Commodity Quantity/ton Total


    Mangala Port Meluit Port Shaft Drive 50.00 $10,820.00
    Total
    like that until the invoice obtains all the entered data and i shall format the printing area to cover the header of paper+ logo up to terms and condition.

    Thank you again wolf!!

  15. #15
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    Well, too late tonight for me to keep working on it, I'll have to get to it tomorrow (12 - 18 hrs, I have to work tommorow )
    But, I will get back to it, it's a very interesting problem, and I think I see a solution without VBA, and have some ideas for solutions in VBA, so...later

  16. #16
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13
    Sleep well if you can :D
    Good night.

  17. #17
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Quote Originally Posted by dredwolf View Post
    Well, too late tonight for me to keep working on it, I'll have to get to it tomorrow (12 - 18 hrs, I have to work tommorow )
    But, I will get back to it, it's a very interesting problem, and I think I see a solution without VBA, and have some ideas for solutions in VBA, so...later
    Hi dredwolf, I hope you're doing well so far, i just wanted to remind you, it is almost 72 hours my friend.

  18. #18
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    Sorry, have had spotty net connections the last few days (ISP upgrading, so a lot of "No Access Available" times )

    Anyhow, try Attached version (Still avoided VBA )
    (Nothing wrong with VBA, I just like to try to do it with formulas, and the built in features..)

    the currency conversion is quite simple, it just multiples the calculated value by the value in cell M9
    as for the last step, you have this 99% done in the Service Price table, I just altered the one column to reflect the proper cells for tonnage

    Hope this helps
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Quote Originally Posted by dredwolf View Post
    Sorry, have had spotty net connections the last few days (ISP upgrading, so a lot of "No Access Available" times )

    Anyhow, try Attached version (Still avoided VBA )
    (Nothing wrong with VBA, I just like to try to do it with formulas, and the built in features..)

    the currency conversion is quite simple, it just multiples the calculated value by the value in cell M9
    as for the last step, you have this 99% done in the Service Price table, I just altered the one column to reflect the proper cells for tonnage

    Hope this helps
    It's more helpful, thank you very much my friend!

  20. #20
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    You are welcome !

    No need to quote the whole post though, just takes up room, if you need to quote something (say the post is far back in the thread") just quote the part that is relevant to your post Okay?

    (you can use the Go Advanced button at the bottom of the Quick reply window to edit out the irrelevant parts if you hit the reply with qoute button on the message you are quoting)

  21. #21
    Registered User
    Join Date
    10-23-2013
    Location
    UAE
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Freight Calculate per distance

    Quote Originally Posted by dredwolf View Post
    No need to quote the whole post though, just takes up room, if you need to quote something (say the post is far back in the thread") just quote the part that is relevant to your post Okay?
    Like that

    Thank you again my valuable friend

  22. #22
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Freight Calculate per distance

    Yep
    and you are welcome !

+ 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. Calculate distance
    By mukesh mishra in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2013, 06:23 AM
  2. 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
  3. How do I calculate the distance between 2 postcodes?
    By Rhys in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2006, 03:30 PM
  4. [SOLVED] calculate time/distance
    By KJO in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2005, 05:06 PM
  5. [SOLVED] CALCULATE DISTANCE BETWEEN TWO GPS POSITIONS
    By Jon re Excel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2005, 11:06 PM

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