# Allocating items to districts using percentages

#### Corticus

##### Well-known Member
Hi all!!

I've been having this problem for as long as I can remember, I think its kind of tricky, so read on...

I allocate devices across the state, to districts, according to a funding formula.

I'll enter the total amount of an item in the total column, and the item should be divided according to the funding formula, as follows:
Book2
BCDEFGHIJKL
2District12345678TotalCalculated
3Formula5%10%15%8%22%24%9%7%100%Total
4Device135841112545052
5Device2123245212020
6Device3481261819768080
7Device4471161517657071
8Device52463910434041
Sheet1

You can see the problem, because I need whole items, the actual sum is off.

Can I make Excel allocate these items while rounding to whole numbers, and still maintain a proper sum??

Thanks as always,
Corticus

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You could ignore the round function but just format your numbers to 0 decimal places.

I only put the Round() function is so I could demonstrate how the actual sum differs from the sum that is split-up.

If you were to exclude the Round(), then manually add up the allocations(which could be formatted to zero decimal places), you would get the same results.

Thanks though, at least my question got bumped up!

Corticus

How about using rounddown to do the calculations...it seems to work with the examples given, but it might fall down if you have total numbers that aren't multiples of 10...

Thanks for the help,

I tried rounddown(), no luck.

My example is actually a simplified version of the allocations I'm trying to do. The table is actually much larger with a lot more districts and items than listed.

This seems to be a pretty tricky problem, I'm suprised I haven't seen it around here before!

As things are now, I have to manually verify each allocation and bump up/down items to make the Sum() correct, which, needless to say, is a real pain considering the size of the table and the frequency with which I have to make modifications.

My only work-around has been to put a "calculated total" as you see, to verify the actual sum versus the total number I data-entried.

I appreciate the help, I never posted this before because I really didn't think there was a way to do what I wanted to do, but around here, that's a challenge!

So I figured, "Let's give it a try"

Thanks again,
Corticus

allocating by percentages

Corticus,

I saw your problem on the board and had to respond. I use spreadsheets to allocate cash (which means figures to two decimal places) using varying percentages and have had similar problems with Excel about allocating across several cells and totaling equally. I find the various ROUND functions won't help with this. (Dividing \$0.25 between two people is \$0.13 and \$0.12 but to Excel, it's \$0.125 and \$0.125 or \$0.12 and \$0.12 or \$0.13 and \$0.13, depending on the ROUND function).

I surfed the Internet a while ago and came across an Excel add-in called Divvyfun. (I don't remember now where I found it.) It can allocate an amount by varying percentages without the differences. I tried it with your sample and it worked. It is used as an array function. You can even allocate up to about 6 decimal places. If you send me your E-mail address, I'll send it to you. Hasn't failed me yet.

Hope this helps.

Hi Corticus:

Not having the background on your operation, I don't know whether the following approach will work in your case -- however you may find it of interest. What I am suggesting is that you have a House Account. The house picks up any difference between the budgeted allocation and the actual rounded allocation. So here goes ...
y030430h1.xls
BCDEFGHIJKLM
2District12345678TotalCalculatedDifference
3Formula5%10%15%8%22%24%9%7%100%TotalHousePickUp
4Device35841112545052-2
5Device1232452120200
6Device471161517657071-1
7Device369513145460591
8Device8162413353814111601591
9-1
Sheet4

Since you mention you have a large number of allocations, theoreticlly the house should break even at the end. You will notice that when I changed some of the Allocation Amounts, the house is behind in some cases, it is ahead in some other cases, and for a large number of cases, it should tend toward breaking even.

I hope this helps. If I have misunderstood your question -- my apologies!

Wow,

Goes to show that your post doesn't have to be on the zero replies list to get a response, or even the first 100 pages!

Thanks a lot guys,

Ricardo2 - I would love to see the add-in, sounds perfect
corticus@hotmail.com

Yogi - Thanks for the reply, your solution is almost exactly what I did, but instead of a "house", I took the values that would have shown up in the "house" column, and redistribute them. When I tried to explain my method to my boss (the "house" column ide), her eyes kind of glazed over, so I just redistributed the difference, and she could whip out her adding maching and see that they add up.

Thanks again!

I got this add-in from Ricardo, its located at:

It works great, so give it whir!

Its a long-shot, but does anyone know a UDF that will do this? Or at least something I could 'ship' with a spreadsheet instead of as an additional .xla file the user would have to put on their maching.

Am I the only who finds add-ins kind of useless? If you're developing a spreadsheet for somebody, you want it to be distributable, and to my knowledge, there is no reasonable method for distributing an add-in with a workbook. I prefer UDFs. They're cool.

Thanks!

Replies
8
Views
492
Replies
11
Views
2K
Replies
10
Views
847
Replies
7
Views
290
Replies
12
Views
965

1,218,625
Messages
6,143,574
Members
450,493
Latest member
Woejeber

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back