Allocating items to districts using percentages


Well-known Member
Apr 30, 2002
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:

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,

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.
Upvote 0
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!

Upvote 0
How about using rounddown to do the seems to work with the examples given, but it might fall down if you have total numbers that aren't multiples of 10...
Upvote 0
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,
Upvote 0
allocating by percentages


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.
Upvote 0
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 ...

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!
Upvote 0

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

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!
Upvote 0
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.

Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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