Calculating Costs

[Pheng]

Hi all,

I need a spreadsheet that will calculate the shipping costs for me.

I want it to be able to allow me to select the destination and ente
the total weight.

The total weight will be calculated as following:

If it is Less Than 15kg, then the Base rate is applied.
If it is Greater than 15Kg the amount over will be multiplied by th
Rate in which the destination lies.

For example:

I select destination, Sydney and the total weight being 9 Kg.

Then the Base rate only applies, which is $10.00.

If it the weight is 25Kg, then the base rate applies plus an amount pe
Kilo over - say $0.75/Kg.

Therefore the total amount is $10.00 + $7.50 (25-15Kg = 10 * 0.75)

But I want to go a bit extra and display the following information whe
a destination is selected from a drop down menu:

- The applicable Rate (1,2,3,4,5 or 6)
- The amount per extra Kg according to rate (0.10, 0.20, 0.30, 0.40
0.50 or 0.60)
- The Base amount
- The amount payable over 15Kg (per Kilo)
- Total amount payabl

 

[Frank Kabel]

Hi
as you haven't provided information how you calculate the
applicable rate (1, ...) you may try the following which
should answer at least the other issues:
1. Create a lookup table with the following layout on a
separate sheet (e.g. call this sheet 'lookup'):
A B C
1 Dest base perkg
2 Sydney 10 0.75
.....


2. Now on a second sheet lets assume you have this drop-
down with the destination in cell A1 and in cell B1 the
weight. Then use the following formulas:
a. Total amount:
=IF(AND(A1<>"",B1<>""),VLOOKUP(A1,'lookup'!$A$1:$C$10,2,0)
+MAX(VLOOKUP(A1,'lookup'!$A$1:$C$10,2,0)*(B1-15),0),"")

b. rate for above 15kg:
=IF(AND(A1<>"",B1<>""),VLOOKUP(A1,'lookup'!
$A$1:$C$10,2,0),"")

c. Base amount:
=IF(AND(A1<>"",B1<>""),VLOOKUP(A1,'lookup'!
$A$1:$C$10,2,0),"")

d. over 15kg amount:
=IF(AND(A1<>"",B1<>""),MAX(VLOOKUP(A1,'lookup'!
$A$1:$C$10,2,0)*(B1-15),0),"")