Weight Calculation Formula

[ayazgreat]

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

<colgroup><col style="width: 30px;"><col style="width: 121px;"><col style="width: 68px;"><col style="width: 78px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[shg]

How did you arrive at the cost for item 2024?

 

[shg]

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]

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

<colgroup><col style="width: 30px;"><col style="width: 160px;"><col style="width: 86px;"><col style="width: 84px;"><col style="width: 90px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[shg]

ADVERTISEMENT

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

 

[ayazgreat]

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

 

[ayazgreat]

ADVERTISEMENT

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

<colgroup><col style="width: 30px;"><col style="width: 160px;"><col style="width: 86px;"><col style="width: 84px;"><col style="width: 90px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[shg]

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]

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]

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?