Excel 2007 : Calculation on the basis of IF

[habibian88]

Can anyone pls assit me on it. It would be most appreciated.

In excel i have to calculate gross weight..gross weight is "net weight+tare weight".
The net weight of a bag could be 50 lb or 25 kg..however the tare weight is always 0.3 kg..if the unit is "kgs" then its obvious that this is 25 kg per bag, and if its "lbs" then for sure its 50 lb per bags.
One more thing, if the code is "rtc" or "ipi" then the conversion from kg to lb will be "2.205", however if the code is other then these two, conversion will always be "2.2"


S. No. | Code | No. Of Bags | Unit | Net Weight | Gross Weight

1--------PPI--------800--------KGS--------20000-------- __________
2--------PPI--------800--------LBS--------20000-------- __________
3--------RTC--------800--------KGS--------20000-------- __________
4--------RTC--------800--------LBS--------20000--------__________
5--------IPI--------800--------KGS--------20000--------__________
6--------IPI--------800--------KGS--------20000--------__________
7--------SOM--------800--------KGS--------20000--------__________
8--------SOM--------800--------KGS--------20000--------__________
9--------BNS--------800--------KGS--------20000--------__________
10--------BNS--------800--------KGS--------20000--------__________

many thanks in advance!

[mrice]

Can you supply a couple of manually calculated values to help test if formulae are giving the correct answers.

[habibian88]

Can you supply a couple of manually calculated values to help test if formulae are giving the correct answers.

Thank you for your reply, i had made a few mistakes in last scenario, here is the corrected one along with Gross Weight.

S. No. | Code | No. Of Bags | Unit | Net Weight | Gross Weight

1--------PPI--------800--------25 KG--------20000 KGS-------- 20240 KGS
2--------PPI--------800--------50 LB--------40000 LBS-------- 18400 KGS
3--------RTC--------800--------25 KG--------20000 KGS-------- 20240 KGS
4--------RTC--------800--------50 LB--------40000 LBS--------18358 KGS
5--------IPI--------800--------25 KG--------20000 KGS--------20240 KGS
6--------IPI--------800--------50 LB--------20000 KGS--------18358 KGS
7--------SOM--------800--------25 KG--------20000 KGS--------20240 KGS
8--------BNS--------800--------25 KG--------20000 KGS--------20240 KGS

LB to KG conversion is "2.2" for all the Codes except "RTC" and "IPI"; for these two is "2.205".
Look forward to your response.

[habibian88]

Can you supply a couple of manually calculated values to help test if formulae are giving the correct answers.

I am extremely sorry, just noticed another correction.....the Tare Weight of LB bags is "0.6 LB" and KG bags is "0.3 KG", previously i told that tare weight is always is "0.3 KG".

for your reference, here is the Gross Weight Calculation of S. No. 1, 2 & 4;

S. No. 1.
No. of Bags.............................800
Weight per Bag........................25 KG
Net Weight...............................20000 KGS (800x25)
Tare Weight...............................240 KGS (0.3x800)

Gross Weight is Net Weight + Tare Weight and is always in KGS.

Gross Weight Final.....................20240 KGS

S. No. 2:
No. of Bags.............................800
Weight per Bag........................50 LB
Net Weight...............................40000 LBS (800x50)
Tare Weight...............................480 LBS (0.6x800)

Gross Weight is Net Weight + Tare Weight and is always in KGS.

Gross Weight.............................40480 LBS
Gross Weight Final....................18400 (40480/2.2)--"2.2" is the conversion for all codes except "RTC" and "IPI"..

S. No. 4:
No. of Bags.............................800
Weight per Bag........................50 LB
Net Weight...............................40000 LBS (800x50)
Tare Weight...............................480 LBS (0.6x800)

Gross Weight is Net Weight + Tare Weight and is always in KGS.

Gross Weight.............................40480 LBS
Gross Weight Final.....................18358 (40480/2.205)--"2.205" is the conversion for "RTC" and "IPI"..

[mrice]

This formula doesn't give exactly the same answers as you manual calculations but seems to fit with my understanding of your explanation.

=IF(RIGHT(E1,3)="KGS",LEFT(E1,LEN(E1)-4)+(C1*0.3) & " KGS",IF(OR(B1="RTC",B1 = "IPI"),INT((LEFT(E1,LEN(E1)-4)/2.205)+(C1*0.3)) & " KGS",INT((LEFT(E1,LEN(E1)-4)/2.2)+(C1*0.3)) & " KGS"))

Paste this into a cell in the first row and copy down.

[habibian88]

This formula doesn't give exactly the same answers as you manual calculations but seems to fit with my understanding of your explanation.

=IF(RIGHT(E1,3)="KGS",LEFT(E1,LEN(E1)-4)+(C1*0.3) & " KGS",IF(OR(B1="RTC",B1 = "IPI"),INT((LEFT(E1,LEN(E1)-4)/2.205)+(C1*0.3)) & " KGS",INT((LEFT(E1,LEN(E1)-4)/2.2)+(C1*0.3)) & " KGS"))

Paste this into a cell in the first row and copy down.

Dear,
Can you pls apply it on the attached File and send back to me..
Many thanks.

[Marcol]

Another way of looking at your problem
Insert a column between "Net Weight" & "Gross Weight"

Then
In E2

Please Login or Register  to view this content.

In F2

Please Login or Register  to view this content.

In G2

Please Login or Register  to view this content.

Drag/Fill all down

Format Column G "Gross Weight"
Number Format > Custom >

Please Login or Register  to view this content.

[Marcol]

Your sample workbook is different from what your sample in post #3 suggested
In G1

Please Login or Register  to view this content.

[habibian88]

Your sample workbook is different from what your sample in post #3 suggested
In G1

Please Login or Register  to view this content.

Great...Thanks very much..

[habibian88]

Another way of looking at your problem
Insert a column between "Net Weight" & "Gross Weight"

Then
In E2

Please Login or Register  to view this content.

In F2

Please Login or Register  to view this content.

In G2

Please Login or Register  to view this content.

Drag/Fill all down

Format Column G "Gross Weight"
Number Format > Custom >

Please Login or Register  to view this content.

Great..thanks very much..