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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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?

Replies
8
Views
226
Replies
2
Views
230
Replies
13
Views
459
Replies
4
Views
100
Replies
3
Views
31

1,109,445
Messages
5,528,800
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...