Weight Calculation Formula

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
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

ABCDEF
2Weight (Kg)Rate - ARate - BRate - C
30.1 to 0.5122025
40.6 to 1.4142227
5Each additional Kg101823
6
7
8 Required Formula in this col
9Item DnRate TypeWeight - KgCost
102021A0.312
112022A1.214
122023B0.520
132024B224
142025B2.424
152026C496
162027C1.427

<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
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
How did you arrive at the cost for item 2024?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jan 19, 2008
Messages
1,151
Thanks shg for your reply please find below correct calculation cost highlighted yellow

Sheet1

ABCDEF
2Weight (Kg)Rate - ARate - BRate - C
30.1 to 0.5122025
40.6 to 1.4142227
5Each additional Kg101823
6 18
7 Required Formula in this col
8Item DnRate TypeWeight - KgCost
92021A0.312
102022A1.214
112023B0.520
122024B240
132025B2.458
142026C496
152027C1.427

<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
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151

ADVERTISEMENT

please find below required explanation

Sheet1

ABCDEFGHIJKLMNOP
1Weight (Kg)Rate ARate BRate C
20.1 to 0.5122025
30.6 to 1.4142227
4Each additional Kg101823
5 Required Formula in this col
6Item DnRate TypeWeight - KgCost
72021A0.312
82022A1.214
92023B0.520
102024B240the weight is great than 0.6 to 1.4 so first rate b is 22 and add 18 as mentioned for each additional kg
112025B2.458the 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
122026C496
132027C1.427

<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
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jan 19, 2008
Messages
1,151
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
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
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?
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top