Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Set of numbers must equal 100

    I have an Access 2010 database. The user will open a list box of items and select as many as he/she wants, then hit next. A continuous form opens with the selected items. Each item has a text box named "percentage". The user must enter a percentage for each item so that the total percentage of all items equals 100.

    That part is easy, but I thought it would be nice to give the user the option of hitting a command button that would enter a number for each item so that the total still equals 100. If the user is working with 4 items, no big deal. Each item would be 25%. But what if the user is working with say 7 items? Is there a formula that will fill these numbers so that each number is + or - one from the others, and there totals equal 100? Oh, and no decimals allowed.

    For instance using 7 items:
    14%
    14%
    14%
    14%
    14%
    15%
    15%
    Equals 100%

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Might be possible, but I doubt that it will be pretty or elegant. You will most likely need to write a custom function in VBA to achieve this. Look in the help file for <strike>modulus</strike> integer division, and see if anything suggests itself.
    (Edited to correct suggestion.)
    Last edited by weejas; 05-10-13 at 07:44.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I don't know if you are looking for SQL to do it or an algorithm.

    If you do it in VBA, you could do something like:

    (All integer math)

    Code:
    div = 100 / 7
    mod  = 100 % 7
    
    loop i = 1 to 7
       Percent[i] = div
       if i > 7 - mod
          Percent[i] += 1
       end
    end

Posting Permissions

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