Divvy Add-in
Divvy.xla is an Excel '97 add-in containing a function called Divvy() that allocates a number to a range of other numbers. Download the zipped file AddIns.zip.
The function has three arguments =Divvy( Amount, ComparedTo, EachRounded )
- Amount = amount to divvy
- ComparedTo = range of numbers to allocate in proportion to
- EachRounded = number of decimal places
The Divvy function is a must have for anyone who has ever needed to split up, allocate, or divvy an amount with respect to other numbers.
It is an array formula that requires Control-Shift-Enter to work. A sample spreadsheet is included in the zip file. Read the ReadMe.txt file for details on its operation.
To use the function, simply highlight the cells where you wish to allocate an amount,
and type the function:
=divvy(amount to divvy, range of cells to compare with, decimals)
When through typing, hit Control-Shift-Enter to create the array formula. You can
also use the Insert Function menu command. The Divvy Function is under User Defined
at the end. Open up DivvySample.xls for a couple of examples.
How does the Divvy Function work?
First, it sums up the 'compare with' range. Then it gets the percent that each
individual cell makes up to that sum. For example, if you are comparing to three cells
that have a 2, 3, and 4, the 2 makes up .222..., the 3 makes up .333..., and the 4 makes
up .444... .
Next, it multiplies the 'amount to divvy' by each percent. Divvying 100 into 2, 3, and 4,
produces 22.222..., 33.333..., and 44.444... .
Then it applies the third argument, decimals or rounding. If no decimals are wanted, 0 is
entered. In our example, this produces 22, 33, and 44.
This is where the Divvy Function does its work. Our example only adds up to 99. We wanted
to allocate 100. But we're 1 shy. How to determine which one gets it. Various schools of
thought exist on this subject. Divvy's method gives it the one that is closest to rounding
up. In our example, the .444 is closer to .5 than the .222 and .333 so it gets it. It was
just a coincidence that the number 4 was the biggest number. Divvy up 100 compared with 2,
3, and 9 and the 3 will get the extra one.
If after applying the rounding it allocates too much, it will take away from the one(s)
that were closest to rounding down.
What should you use the Divvy function for?
Anything that you need to allocate. For 401(k) plans, you might want to allocate
earnings to employee balances. Split profits among your plant locations. Maybe you
need to divide 435 US representatives among the 50 states.
Some caveats. When allocating an amount against two numbers that are equal, the sort order
will determine the split. For example, divvy 3 against 2 and 2, and the result is 1 and 2.
Also, array formulas cannot be sorted. Therefore, enter your Divvy function, get your
result, then pastespecial the values.
At only 29KB, the Divvy Function Add-in has a very small footprint.
Example
Amount to allocate |
11 |
Relative to |
Divvied |
1 |
2 |
2 |
3 |
4 |
6 |
The program has been checked, but you are encouraged to check it yourself before relying on it. It is complimentary from us, Dean & Company, to anyone interested.
