How to get the amount charged

[gaurav_rastogi]

we have a party who charges us for courier servies like this

country name Zone
India A
USA B
UK C

RATES FOR ZONE A
A. FOR PARCELS
UPTO 100 GMS 250
FROM 101-500 GMS 500
FOR NEXT EVERY 500 GMS 100
B. FOR DOCUMENTS
UPTO 100 GMS 350
FROM 101-500 GMS 600
FOR NEXT EVERY 500 GMS 200

NOW IF SEND A PARCEL TO INDIA WEIGHING 4.5 KG, HOW CAN I CALCULATE THE AMOUNT TO BE CHARGED?

 

[jolivanes]

If we can assume that we have more or less 1000 grams in a kilogram then
=(((4.5*1000)-500)/500)*100+500 which should cost you 1,300 of some kind of currency.
Make sure to check these numbers as it has been 55+ years since I went to school for this kind of math.

Note:
You used a nice round weight (4.5 kg) here. You might need to round the weight up to the nearest .5 kg (500 gram)
if your weights are not a multiple of 500.

 

[gaurav_rastogi]

I wanted to create a excel sheet so that if i put the name of the country and Parcel/documents as the type of courier and the weight of the parcel/document, the frieght amount is automatically charged.

 

[R Pelle]

Perhaps not the most elegant solution....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	PARCELS	Real	Courier	<=100 gram	101-500 gr	>500 per 500				Courier
2	Country	Weight	Weight	Weight Units 1	Weight Units 2	Weight Units 3	Price 1	Price 2	Price 3	to be paid
3	India	1	100	1	0	0	$ 12.50	$ -	$ -	$ 12.50
4	India	99	100	1	0	0	$ 12.50	$ -	$ -	$ 12.50
5	India	100	100	1	0	0	$ 12.50	$ -	$ -	$ 12.50
6	India	101	500	0	1	0	$ -	$ 15.00	$ -	$ 15.00
7	India	499	500	0	1	0	$ -	$ 15.00	$ -	$ 15.00
8	India	500	500	0	1	0	$ -	$ 15.00	$ -	$ 15.00
9	India	501	1000	0	1	1	$ -	$ 15.00	$ 4.00	$ 19.00
10	India	999	1000	0	1	1	$ -	$ 15.00	$ 4.00	$ 19.00
11	India	1000	1000	0	1	1	$ -	$ 15.00	$ 4.00	$ 19.00
12	India	1001	1500	0	1	2	$ -	$ 15.00	$ 8.00	$ 23.00
13	India	1499	1500	0	1	2	$ -	$ 15.00	$ 8.00	$ 23.00
14	India	1500	1500	0	1	2	$ -	$ 15.00	$ 8.00	$ 23.00
15	India	1501	2000	0	1	3	$ -	$ 15.00	$ 12.00	$ 27.00
16	India	1999	2000	0	1	3	$ -	$ 15.00	$ 12.00	$ 27.00
17	India	2000	2000	0	1	3	$ -	$ 15.00	$ 12.00	$ 27.00
18	India	2001	2500	0	1	4	$ -	$ 15.00	$ 16.00	$ 31.00
19	India	2499	2500	0	1	4	$ -	$ 15.00	$ 16.00	$ 31.00
20	India	2500	2500	0	1	4	$ -	$ 15.00	$ 16.00	$ 31.00
21	India	2501	3000	0	1	5	$ -	$ 15.00	$ 20.00	$ 35.00
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C3	=IF(B3<=100,100,IF(B3<=500,500,CEILING(B3,500)))
D3	=IF(AND(B3>0,B3<=100),1,0)
E3	=IF(B3>100,1,0)
F3	=IF(B3>500,INT((C3-500)/500),0)
G3	=IFERROR(VLOOKUP($A3,$L$2:$O$5,2,0),0)*D3
H3	=IFERROR(VLOOKUP($A3,$L$2:$O$5,3,0),0)*E3
I3	=IFERROR(VLOOKUP($A3,$L$2:$O$5,4,0),0)*F3
J3	=SUM(G3:I3)

Two lookup tables:

Excel Workbook
	L	M	N	O
1	Lookup table
2	PARCELS	0-100g	101-500g	>500g per 500g
3	India	$ 12.50	$ 15.00	$ 4.00
4	UK	$ 20.00	$ 25.00	$ 6.00
5	US	$ 4.00	$ 6.00	$ 3.00
6
7	Lookup table
8	DOCUMENTS	0-100g	101-500g	>500g per 500g
9	India	$ 2.50	$ 5.00	$ 1.75
10	UK	$ 7.00	$ 18.00	$ 2.50
11	US	$ 1.50	$ 3.75	$ 0.99
Sheet1

Excel Workbook
	A	B	C	D	E	F	G
51	PARCELS	Real	Courier	<=100 gram	101-500 gr	>500 per 500	Courier
52	Country	Weight	Weight	Price 1	Price 2	Price 3	to be paid
53	India	0	100	$ -	$ -	$ -	$ -
54	India	90	100	$ 12.50	$ -	$ -	$ 12.50
55	India	100	100	$ 12.50	$ -	$ -	$ 12.50
56	India	101	500	$ -	$ 15.00	$ -	$ 15.00
57	India	499	500	$ -	$ 15.00	$ -	$ 15.00
58	India	500	500	$ -	$ 15.00	$ -	$ 15.00
59	India	501	1000	$ -	$ 15.00	$ 4.00	$ 19.00
Excel 2010 and after some additional tweaking.... Sheet1

Excel 2010

Cell Formulas
Range	Formula
C53	=IF(B53<=100,100,IF(B53<=500,500,CEILING(B53,500)))
D53	=IF(AND(B53>0,B53<=100),IFERROR(VLOOKUP($A53,Sheet1!$L$2:$O$5,2,0),0),0)
E53	=IF(B53>100,IFERROR(VLOOKUP($A53,Sheet1!$L$2:$O$5,3,0),0),0)
F53	=IF(B53>500,INT((C53-500)/500)*IFERROR(VLOOKUP($A53,Sheet1!$L$2:$O$5,4,0),0),0)
G53	=SUM(D53:F53)

Of course you only need the first row to calculate. The others are there so you can see the results it produces for different weights.
Your input would be Cells A3 and B3.

You also need to duplicate this calculation to take care of Documents.


Rob

 

[R Pelle]

Not acknowledging responses given to your question is considered bad form.