Results 1 to 8 of 8

Thread: Help Calculation in dozens and units

  1. #1
    Seeker SilverChat's Avatar
    Join Date
    Feb 2015
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Help Calculation in dozens and units



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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....

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,721
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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)

  3. #3
    Seeker SilverChat's Avatar
    Join Date
    Feb 2015
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Thanks alot....
    Thanks it worked.....
    GOD BLESS YOU

  4. #4
    Seeker SilverChat's Avatar
    Join Date
    Feb 2015
    Posts
    7
    Articles
    0
    Excel Version
    2016
    what if i want to add carton column then what would be the formula.????
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	15r0d38.jpg 
Views:	5 
Size:	31.8 KB 
ID:	3283  
    Last edited by SilverChat; 2015-03-13 at 03:53 AM.

  5. #5
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    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.

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    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)

  7. #7
    Seeker SilverChat's Avatar
    Join Date
    Feb 2015
    Posts
    7
    Articles
    0
    Excel Version
    2016
    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)

  8. #8
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    pl find attachment
    Attached Files Attached Files

Posting Permissions

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