# Weight Calculation Formula

#### ayazgreat

##### Well-known Member
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.

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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### shg

##### MrExcel MVP
How did you arrive at the cost for item 2024?

#### shg

##### MrExcel MVP
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

##### Well-known Member

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

##### MrExcel MVP

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

#### ayazgreat

##### Well-known Member
Your given formula is not working and result is # N/A

#### ayazgreat

##### Well-known Member

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

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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?

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,627
Messages
5,838,441
Members
430,548
Latest member
hh_dh2001

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back