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
 

Some videos you may like

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
Joined
May 7, 2008
Messages
21,770
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,770
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,770
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,770
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,770
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?
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top