+ Reply to Thread
Results 1 to 4 of 4

Automatically calculating amount of labels populating a sheet

  1. #1
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    Automatically calculating amount of labels populating a sheet

    I got a math equation that I wish to get to work within excel.

    I'm basically calculating how many labels can go on a specific sheet. The sheet is sized as 14.875" x 11". The label is 3.375" x .75". We have gutters on the left and right side of this sheet that calculate to .375" with top and bottom gutters measuring out to .125". Inbetween each label, whether it is to the side, above or below the label is also a .125" gap. So a fun little formula I know can works is:

    =(.75*12)+.375+(.125*12) Note: 12 is assumed to be the # of labels up.

    =(3.375*4)+.375+.375+(.125*3) Note: 3 is the assumed # of gutters inbetween the labels, and the 4 is the assumed number of labels that populate a row.

    The correct answer is 48, but I want to create a formula that doesn't assume the number of labels that can fit up the page and side to side. I want to figure out how many labels will populate a sheet with just the dimensions of the page, the dimension of the label, the gutter dimensions inbetween and on the outside of the sheet. So instead of checking will 48 labels fill the page or will 44 fill the page, I want to have the formula do it for me. Not sure if this is possible, but thank you ahead of time to anyone who might have a solution.
    Last edited by Karroog; 09-02-2009 at 03:19 PM. Reason: I did screw up the title, didn't I? :P

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Need help with complex math

    Your question is straightforward, but your title is awful. Please read the forum rules, then change it.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: Need help with complex math

    Use TRUNC function to divide (sheet dimension less two gutters) by (label dimension plus space between label)

    So TRUNC((14.875-2*.125)/(.75+.125)) and TRUNC((14.875-2*0.375)/(3.375+0.125))

    EDIT: actually, that will be off by one spacer...

    to fix that, subtract one label width from the top and then add 1 to the result...

    TRUNC((14.875-2*.125-.75)/(.75+.125))+1 and TRUNC((14.875-2*0.375-3.375)/(3.375+0.125))+1
    Last edited by masteff; 09-02-2009 at 03:20 PM.

  4. #4
    Registered User
    Join Date
    08-08-2007
    Posts
    80

    Re: Automatically calculating amount of labels populating a sheet

    Dude I thought about using TRUNC but I didn't think it would work. Thanks for showing it, made my day :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1