# Thread: Set of numbers must equal 100

1. Registered User
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. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,070
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 06:44.

3. Registered User
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
•