Help Calculation in dozens and units

S

SilverChat

New member
Joined
Feb 28, 2015
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
AVAILABLE DATASALE DATAREMAINING DATA
DOZENS
UNITS
DOZENS
UNITS
DOZENS
UNITS
531639

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.????
15r0d38.jpg
 

Attachments

  • 15r0d38.jpg
    15r0d38.jpg
    31.8 KB · Views: 12
Last edited:
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
 

Attachments

  • sample.xlsx
    8.7 KB · Views: 26
You must log in or register to reply here.
Back
Top