Freight calculator

Download "freight.xls" (click here) [1]. Ignore any preview errors. Just download the file.

[1] https://app.box.com/s/zu216je7lunf4mtjgv017syjtuf6nmiy

Venkat wrote:

Example -1

Weight of the shipment : 70Kgs

Type : Import

Zone: 1

Freight in this case should show as 726.38 ( refer to the above Import table).

And that is exactly what I get with the formula that I posted, when the references to column B are changed appropriate.  See Example 1 in the image below.

Venkat wrote:

Example -2

Weight of the shipment : 150kgs

Type : Export

Zone: 3

Freight in this case will be 

for the first 70 kgs              =1600.76

Balance 80 kgs = 22.68*30 =  680.40 (>70kgs rate to be considered)

Total                                    = 2281.16

And that is exactly what I would get if the the total weight were __100__, so that the balance is 30 per your calculation on the right.  See Example 2 in the image below.

But the last I checked, if the total is __150__, the balance is __80__ as you show on the left, not 30. So 22.68 should be multiplied by __80__, not 30.  See Example 3 in the image below.

Venkat wrote:

Also while the user chooses the Type (" Export"/ "Import"), the Freight value  should be calculated based the values from the appropriate table to give the correct output.

And that is exactly how the formula behaves, as demonstrated by the examples in the image below.

I suspect that you neglected to update the references to column B appropriately when you set up your examples.

If my downloaded file doesn't clear things up for you, upload an Excel file that demonstrates the formulas and calculations for your examples to a file-sharing website (e.g. box.net/files) and post the public/share URL. Test the download URL, being careful to log out of the file-sharing website first.

Demonstration....

Formulas:

B6:

=IF(B2="export",

IF(B4>$D$4, ROUND(HLOOKUP(B3,$E$2:$G$4,3)+(B4-$D$4)*HLOOKUP(B3,$E$2:$G$4,2),2),

                         INDEX($E$4:$G$55,MATCH(B4,$D$4:$D$55,-1),MATCH(B3,$E$2:$G$2,0))),

IF(B4>$D$4, ROUND(HLOOKUP(B3,$H$2:$J$4,3)+(B4-$D$4)*HLOOKUP(B3,$H$2:$J$4,2),2),

                         INDEX($H$4:$J$55,MATCH(B4,$D$4:$D$55,-1),MATCH(B3,$H$2:$J$2,0))))

B13:

=IF(B9="export",

IF(B11>$D$4, ROUND(HLOOKUP(B10,$E$2:$G$4,3)+(B11-$D$4)*HLOOKUP(B10,$E$2:$G$4,2),2),

                         INDEX($E$4:$G$55,MATCH(B11,$D$4:$D$55,-1),MATCH(B10,$E$2:$G$2,0))),

IF(B11>$D$4, ROUND(HLOOKUP(B10,$H$2:$J$4,3)+(B11-$D$4)*HLOOKUP(B10,$H$2:$J$4,2),2),

                         INDEX($H$4:$J$55,MATCH(B11,$D$4:$D$55,-1),MATCH(B10,$H$2:$J$2,0))))

B20:

=IF(B16="export",

IF(B18>$D$4, ROUND(HLOOKUP(B17,$E$2:$G$4,3)+(B18-$D$4)*HLOOKUP(B17,$E$2:$G$4,2),2),

                         INDEX($E$4:$G$55,MATCH(B18,$D$4:$D$55,-1),MATCH(B17,$E$2:$G$2,0))),

IF(B18>$D$4, ROUND(HLOOKUP(B17,$H$2:$J$4,3)+(B18-$D$4)*HLOOKUP(B17,$H$2:$J$4,2),2),

                         INDEX($H$4:$J$55,MATCH(B18,$D$4:$D$55,-1),MATCH(B17,$H$2:$J$2,0))))

Thank you Joe for your solution provided. However I am still not getting the required correct values.

Would like to summarize with the below examples for me to explain it to you.

Example -1

Weight of the shipment : 70Kgs

Type : Import

Zone: 1

Freight in this case should show as 726.38 ( refer to the above Import table).

Example -2

Weight of the shipment : 150kgs

Type : Export

Zone: 3

Freight in this case will be 

for the first 70 kgs              =1600.76

Balance 80 kgs = 22.68*30 =  680.40 (>70kgs rate to be considered)

Total                                    = 2281.16

( Refer to the Export table under Zone 3 for the above values)

Also while the user chooses the Type (" Export"/ "Import"), the Freight value  should be calculated based the values from the appropriate table to give the correct output.

Hope the above explanation helps and makes sense in what I wanted to state. If you could kindly assist and provide me with the appropriate formula.

Regards,

Venkat

The following is one way to organize the data and formulate a solution.

First, I suggest that you sort the tables in reverse order, as shown.

The formula in B5 is:

=IF(B1="export",

IF(B3>$D$4, ROUND(HLOOKUP(B2,$E$2:$G$4,3)+(B3-$D$4)*HLOOKUP(B2,$E$2:$G$4,2),2),

            INDEX($E$4:$G$55,MATCH(B3,$D$4:$D$55,-1),MATCH(B2,$E$2:$G$2,0))),

IF(B3>$D$4, ROUND(HLOOKUP(B2,$H$2:$J$4,3)+(B3-$D$4)*HLOOKUP(B2,$H$2:$J$4,2),2),

            INDEX($H$4:$J$55,MATCH(B3,$D$4:$D$55,-1),MATCH(B2,$H$2:$J$2,0))))

Use the Formulas > Evaluate Formula operation to see how the formula works.

Notes:

1. I assume the weight and cost apply to the total number of items (B4). If they are per item, change the beginning of the formula to =**B4 ***IF(...).
2. When the weight exceeds D4 (70), I assume the excess weight is prorated. If the excess weight should be rounded up to an integer, change (B3-$D$4) to ROUNDUP(B3-$D$4, 0) .