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)
AVAILABLE DATA SALE DATA REMAINING DATA DOZENS
UNITS
DOZENS
UNITS
DOZENS
UNITS
5 3 1 6 3 9
Hi, every one please help me out on this....
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....
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)
Thanks alot....
Thanks it worked.....
GOD BLESS YOU
what if i want to add carton column then what would be the formula.????
![]()
Last edited by SilverChat; 2015-03-13 at 04:53 AM.
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
Is it correct? Please check your data.
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)
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)
pl find attachment
Bookmarks