Excel 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 )

  1. Amount = amount to divvy
  2. ComparedTo = range of numbers to allocate in proportion to
  3. 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.

 

If you have questions or comments, please contact us at Dean & Company.

Home