Allocating items to districts using percentages

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
You could ignore the round function but just format your numbers to 0 decimal places.
 

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

Board Regular
Joined
Jun 26, 2002
Messages
109
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

Well-known Member
Joined
Apr 30, 2002
Messages
1,579

ADVERTISEMENT

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

New Member
Joined
Apr 8, 2002
Messages
14
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

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!
 

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
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

New Member
Joined
May 1, 2003
Messages
21
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!
 

Forum statistics

Threads
1,143,735
Messages
5,720,561
Members
422,291
Latest member
Deveshk

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