+ Reply to Thread
Results 1 to 14 of 14

Formula Lookup? (Making a Freight Calculator)

  1. #1
    Registered User
    Join Date
    03-31-2009
    Location
    Sydney,OZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Formula Lookup? (Making a Freight Calculator)

    Hello All

    Im fairly new to big formulations in excel

    im trying to make a Freight Calculator

    Where you will enter the Region(B1:F1) to a cell B11 and weight(A2:A7) into Cell B12 and it will return the rate(B2:F7) to Cell B13 multiplied by the weight

    i work out how to do it with entering the Exact weight listed but if the weight is different it would return an error

    so it need to find the rate between a certain range? im not sure if this makes sense

    ( See attached )

    thanks for your help
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013(work), Excel 2013(home)
    Posts
    206

    Re: Formula Lookup? (Making a Freight Calculator)

    Do you use linear interpolation for calculating the weight when it is not exact? (y = mx + b)
    If you can't figure out how a formula works, try stepping through it using "Evaluate Formula" in the Formula Auditing menu item in the tools menu!

    If you want to see where your code went wrong, try stepping through it by clicking in the code and pressing F8 and watch as the magic happens!


    If you are happy with any of the results, please add to the contributor's reputation by clicking the star icon.

  3. #3
    Registered User
    Join Date
    03-31-2009
    Location
    Sydney,OZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula Lookup? (Making a Freight Calculator)

    Well thats what im trying to work out.. im not sure how its done

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Formula Lookup? (Making a Freight Calculator)

    Try this in B13: =INDEX($B$2:$F$7, MATCH(B12, $A$2:$A$7, 1), MATCH(B11, $B$1:$F$1, 0)) * B12

    Or, if you select cells A1:F7, and then in the Names box, left of the Formula bar, type "Table" (sans quotes), you can use this:

    =INDEX(Table, MATCH(B12, INDEX(Table, 0, 1) ), MATCH(B11, INDEX(Table, 1, 0) ) ) * B12
    Last edited by shg; 03-31-2009 at 08:10 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    03-31-2009
    Location
    Sydney,OZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula Lookup? (Making a Freight Calculator)

    Thanks SHG

    i think that did it!

    if i want it on another sheet mainly to hide the table
    would be something like this?

    =INDEX(Sheet1!Table, MATCH(B12, INDEX(Sheet1!Table, 0, 1) ), MATCH(B11, INDEX(Sheet1!Table, 1, 0) ) ) * B12

  6. #6
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013(work), Excel 2013(home)
    Posts
    206

    Re: Formula Lookup? (Making a Freight Calculator)

    This assumes you are linearly interpolating between the weight values. I hope it works for you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-31-2009
    Location
    Sydney,OZ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula Lookup? (Making a Freight Calculator)

    Hey Everstrivin

    thats really cool how that works

    but the freight rate dose not change to the weight it just works on the range

    eg.
    501 to 1000 is at .134

    so 564 will still be .134

  8. #8
    Forum Contributor
    Join Date
    03-12-2009
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013(work), Excel 2013(home)
    Posts
    206

    Re: Formula Lookup? (Making a Freight Calculator)

    Do you need me to fix it?

  9. #9
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Formula Lookup? (Making a Freight Calculator)

    Mike,

    If you're set, please mark the thread as solved.

    If not, post your workbook as you wish it arranged.

  10. #10
    Registered User
    Join Date
    07-13-2011
    Location
    sydney
    MS-Off Ver
    Excel 2007
    Posts
    3

    Building a Freight Calculator

    I need assistance in finishing my project to build a freight calculator for my company. I have a week to finish and I am quite slow in formula. Please help.

    The database I built is in “RateData” tab, and i need to simplify into each table for the full load rate and half load rate. I am confused to get the lookup formula work. I need to match the carrier, from, and to with “FTL” or “LTL” and then looking up for the rate value in the total (for Full Load Rate) or single space rate (for half load rate).

    Thank you so much for helping me out.

    Cheers
    Desperato
    Attached Files Attached Files
    Last edited by desperato; 07-14-2011 at 05:55 AM.

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673

    Re: Formula Lookup? (Making a Freight Calculator)

    Welcome to the forum.

    Please take a fww minutes to read the forum rules (link in menu bar), and then start your own thread.

    Thanks.

  12. #12
    Registered User
    Join Date
    01-29-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Building a Freight Calculator

    Hello I am preparing on excel sheet and in that i need a help.
    I am preparing rate sheet in that what i want is as per destination and weight cell should pick up the rate.
    But must consider the weight breaks.
    for example destination is ARN and weight is 100kgs that rate should be 135 like wise for all the destination.
    please help me completing my project I am attaching my file also.Rate data.xlsx

  13. #13
    Registered User
    Join Date
    01-29-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Building a Freight Calculator

    Hello I am preparing on excel sheet and in that i need a help.
    I am preparing rate sheet in that what i want is as per destination and weight cell should pick up the rate.
    But must consider the weight breaks.
    for example destination is ARN and weight is 100kgs that rate should be 135 like wise for all the destination.
    please help me completing my project I am attaching my file also.
    Attached Files Attached Files

  14. #14
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Formula Lookup? (Making a Freight Calculator)

    Welcome to the forum.

    Please take a fww minutes to read the forum rules (link in menu bar), and then start your own thread.

    Thanks.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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