# Formula Lookup? (Making a Freight Calculator)

1. ## 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 )

2. ## Re: Formula Lookup? (Making a Freight Calculator)

Do you use linear interpolation for calculating the weight when it is not exact? (y = mx + b)

3. ## Re: Formula Lookup? (Making a Freight Calculator)

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

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

5. ## 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. ## Re: Formula Lookup? (Making a Freight Calculator)

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

7. ## 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. ## Re: Formula Lookup? (Making a Freight Calculator)

Do you need me to fix it?

9. ## Re: Formula Lookup? (Making a Freight Calculator)

Mike,

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

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

11. ## Re: Formula Lookup? (Making a Freight Calculator)

Welcome to the forum.

Thanks.

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

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

14. ## Re: Formula Lookup? (Making a Freight Calculator)

Welcome to the forum.

Thanks.

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

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