# Thread: Help Calculation in dozens and units

1. ## Help Calculation in dozens and units

 AVAILABLE DATA SALE DATA REMAINING DATA DOZENS UNITS DOZENS UNITS DOZENS UNITS 5 3 1 6 3 9

I have to make the table in which these are the columns and rows where Available data is the stock available and sale data is the one which i want to insert manually but remaining data will automatically be calculated,
for suppose I have available data dozens and units I have sold dozens and units now the remaining data will automatically be subtracted from available data....  Reply With Quote

2. Supposing your data is in row 2, remaining dozens is =QUOTIENT((A2-C2)*12+B2-D2,12) and remaining units =MOD((A2-C2)*12+B2-D2,12)  Reply With Quote

3. Thanks alot....
Thanks it worked.....
GOD BLESS YOU  Reply With Quote

4. what if i want to add carton column then what would be the formula.????   Reply With Quote

5. As per screen shots attach by you,

Available Data
Cartoon = 10 Cart x 72 = 720 Unit
Dozen = 5 Doz x 12 = 60 Unit
Unit = 7 Unit

Total = 787 Unit

Sale Data
Cartoon = 1 Cart x 72 = 72 Unit
Dozen = 7 Doz x 12 = 84 Unit
Unit = 3 Unit

Total = 159 Unit

Remaining Data
Cartoon = 8 Cart x 72 = 576 Unit
Dozen = 10 Doz x 12 = 120 Unit
Unit = 4 Unit

Total = 700 Unit

It should be 787-159=628 Unit
As per your data it is 787-159 = 700 Unit  Reply With Quote

6. Try

=INT((SUMPRODUCT(A2:C2,{72,12,1})-SUMPRODUCT(D2:F2,{72,12,1}))/72)
=INT(((SUMPRODUCT(A2:C2,{72,12,1})-SUMPRODUCT(D2:F2,{72,12,1}))-G2*72)/12)
=INT((SUMPRODUCT(A2:C2,{72,12,1})-SUMPRODUCT(D2:F2,{72,12,1}))-G2*72-H2*12)  Reply With Quote

7. sambit and Bob Phillips

Thanks a lot for help

this formula worked....

Cartons =A3-D3-(E3>B3)
Dozens =MOD(B3-E3-(F3>C3) ,6)
Units =MOD(C3-F3, 12)  Reply With Quote

8. pl find attachment  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•