Weight Calculation Formula

ayazgreat · Sep 25, 2014

Hi All

I need a formula for weight calculation, first table shows weight type and their different rate and second table is weight calculation where I require formula in cost column as per first per first table weight rate.

Thanks in advance

Sheet1

	A	B	C	D	E	F
2	Weight (Kg)	Rate - A	Rate - B	Rate - C
3	0.1 to 0.5	12	20	25
4	0.6 to 1.4	14	22	27
5	Each additional Kg	10	18	23
6
7
8				Required Formula in this col
9	Item Dn	Rate Type	Weight - Kg	Cost
10	2021	A	0.3	12
11	2022	A	1.2	14
12	2023	B	0.5	20
13	2024	B	2	24
14	2025	B	2.4	24
15	2026	C	4	96
16	2027	C	1.4	27

Excel tables to the web >> Excel Jeanie HTML 4

shg · Sep 25, 2014

How did you arrive at the cost for item 2024?

shg · Sep 25, 2014

Pending that,

	A	B	C	D	E
1	Wgt\Rate	A	B	C
2	0	12	20	25
3	0.6	14	22	27
4	1.4	10	18	23
5
6	Item	Rate	Wgt	Cost
7	2021	A	0.3	12	D7: =INDEX($B$2:$D$3, MATCH(C7, $A$2:$A$3), MATCH(B7, $B$1:$D$1)) + MAX(0, CEILING(C7 - $A$4, 1) * INDEX($B$4:$D$4, MATCH(B7, $B$1:$D$1)))
8	2022	A	1.2	14
9	2023	B	0.5	20
10	2024	B	2.0	40
11	2025	B	2.4	40
12	2026	C	4.0	96
13	2027	C	1.4	27

ayazgreat · Sep 25, 2014

Thanks shg for your reply please find below correct calculation cost highlighted yellow

Sheet1

	A	B	C	D	E	F
2	Weight (Kg)	Rate - A	Rate - B	Rate - C
3	0.1 to 0.5	12	20	25
4	0.6 to 1.4	14	22	27
5	Each additional Kg	10	18	23
6			18
7				Required Formula in this col
8	Item Dn	Rate Type	Weight - Kg	Cost
9	2021	A	0.3	12
10	2022	A	1.2	14
11	2023	B	0.5	20
12	2024	B	2	40
13	2025	B	2.4	58
14	2026	C	4	96
15	2027	C	1.4	27

Excel tables to the web >> Excel Jeanie HTML 4

shg · Sep 25, 2014

I see that your results are different; what I asked was that you explain how you arrived at them.

ayazgreat · Sep 25, 2014

Your given formula is not working and result is # N/A

ayazgreat · Sep 25, 2014

please find below required explanation

Sheet1

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Weight (Kg)	Rate A	Rate B	Rate C
2	0.1 to 0.5	12	20	25
3	0.6 to 1.4	14	22	27
4	Each additional Kg	10	18	23
5				Required Formula in this col
6	Item Dn	Rate Type	Weight - Kg	Cost
7	2021	A	0.3	12
8	2022	A	1.2	14
9	2023	B	0.5	20
10	2024	B	2	40	the weight is great than 0.6 to 1.4 so first rate b is 22 and add 18 as mentioned for each additional kg
11	2025	B	2.4	58	the weight is great than 0.6 to 1.4 so first rate b is 22 and add 18+18 as mentioned for each additional kg
12	2026	C	4	96
13	2027	C	1.4	27

Excel tables to the web >> Excel Jeanie HTML 4

shg · Sep 25, 2014

That's not what I see. Look at the red cells:

	A	B	C	D	E
1	Wgt\Rate	A	B	C
2	0.0	12	20	25
3	0.6	14	22	27
4	1.4	10	18	23
5
6
7
8	Item	Rate	Wgt	Cost
9	2021	A	0.3	12	D9: =INDEX($B$2:$D$3, MATCH(C9, $A$2:$A$3), MATCH(B9, $B$1:$D$1)) + MAX(0, CEILING(C9 - $A$4, 1) * INDEX($B$4:$D$4, MATCH(B9, $B$1:$D$1)))
10	2022	A	1.2	14
11	2023	B	0.5	20
12	2024	B	2.0	40
13	2025	B	2.4	40
14	2026	C	4.0	96
15	2027	C	1.4	27

ayazgreat · Sep 25, 2014

Shg please note that there are 3 different kind of weight calculation having different type Rate first weight condition is 0. 1 to 0.5 and second 0.6 to 1.4 and third is each additional k g after 1.4 and there are different types of rates.

shg · Sep 25, 2014

For item 2025,

the weight is greater than 0.6 to 1.4 so first rate B is 22 and add 18+18 as mentioned for each additional kg

The rate is $22 for the first 1.4. The actual weight is 2.4. That's ONE additional $18, not two, no?

Weight Calculation Formula

ayazgreat

Well-known Member

Some videos you may like

Excel Facts

shg

MrExcel MVP

shg

MrExcel MVP

ayazgreat

Well-known Member

shg

MrExcel MVP

ayazgreat

Well-known Member

ayazgreat

Well-known Member

shg

MrExcel MVP

ayazgreat

Well-known Member

shg

MrExcel MVP

Similar threads

Watch MrExcel Video

Forum statistics

Share this page

This Week's Hot Topics