Allocating items to districts using percentages

[Corticus]

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
	B	C	D	E	F	G	H	I	J	K	L
2	District	1	2	3	4	5	6	7	8	Total	Calculated
3	Formula	5%	10%	15%	8%	22%	24%	9%	7%	100%	Total
4	Device1	3	5	8	4	11	12	5	4	50	52
5	Device2	1	2	3	2	4	5	2	1	20	20
6	Device3	4	8	12	6	18	19	7	6	80	80
7	Device4	4	7	11	6	15	17	6	5	70	71
8	Device5	2	4	6	3	9	10	4	3	40	41
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

 

[inarbeth]

You could ignore the round function but just format your numbers to 0 decimal places.

 

[Corticus]

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

 

[ThePencilQueen]

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

 

[Corticus]

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

 

[Ricardo2]

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.

 

[Yogi Anand]

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
	B	C	D	E	F	G	H	I	J	K	L	M
2	District	1	2	3	4	5	6	7	8	Total	Calculated	Difference
3	Formula	5%	10%	15%	8%	22%	24%	9%	7%	100%	Total	HousePickUp
4	Device	3	5	8	4	11	12	5	4	50	52	-2
5	Device	1	2	3	2	4	5	2	1	20	20	0
6	Device	4	7	11	6	15	17	6	5	70	71	-1
7	Device	3	6	9	5	13	14	5	4	60	59	1
8	Device	8	16	24	13	35	38	14	11	160	159	1
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!

 

[Corticus]

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!

 

[Cortici]

I got this add-in from Ricardo, its located at:
http://www.retsource.com/programs/divvyaddin.html

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!