Distribute Whole Numbers Based on Percentages
Hello,
I am looking for a way to distribute a whole number based on percentages and return whole numbers. For example, if I have a column of 4 percentages (A1: 38%, A2: 42%, A3: 16%, A4: 4%) that sum to 100% and want to distribute the number 3 according to those percentages it would return B1: 1, B2: 2, B3: 0, B4: 0.
Any help is very much appreciated, thank you!
Hi and welcome to the forum!
Could you please explain the logic behind your distribution in that example? Perhaps also give a few more examples with your expected results?
Regards
Absolutely! Thank you for the warm welcome as well! Basically, I am trying to assign the appropriate number of employees to a ZIP Code by percentage of total volume (so it always needs to be a whole number).
For example, my expected result would be if I have 4 employees and 3 ZIPs with the following volumes:
ZIP A 50% Volume = 2 Employees
ZIP B 25% Volume = 1 Employee
ZIP C 25% Volume = 1 Employee
-or-
4 Employees
ZIP A 50% Volume = 2 Employees
ZIP B 17% Volume = 1 Employee
ZIP C 20% Volume = 1 Employee
ZIP D 13% Volume = 0 Employees
Thank you again!
Thanks, but I'm still not clear on your logic.
The first example is obvious, since those volumes work perfectly when distributed over 4 employees.
Your second, however, isn't so obvious to me. Can you clarify what logic/arithmetic you used to arrive at ZIPs B and C having 1 employee, but ZIP D having 0?
At what point, for example, would ZIP B take up the other 2 employees, leaving both ZIP C and ZIP D with 0?
Regards
Thank you for the quick reply! To give further context on the logic as requested for this example, the thought process is to spread the employees to the largest chunk of volume. In this example, if two employees are in ZIP A they are still getting 25% of the total volume (half each of ZIP A) which is higher than putting an employee in ZIP D which only has 13% of the total volume. Hopefully that helps (I appreciate the patience)!
4 Employees
ZIP A 50% Volume = 2 Employees
ZIP B 17% Volume = 1 Employee
ZIP C 20% Volume = 1 Employee
ZIP D 13% Volume = 0 Employees
Thanks. So is it possible to have an actual workbook with an example of how you're laying this out and where the results should go? What determines how many ZIPs there are? What determines how many employees there are?
I think that workbook really is necessary!
Regards
I have attached an example workbook, I hope I did it correctly (apologies if I did not). I need the formula solve for C, but I put in what I expect for results.
As far as the employees and ZIPs, it will be very variable, so I am unsure how to give that example per se. Just looking for the basic how to formula on how to distribute the numbers properly that I can use on variable numbers of employees and ZIPs (sorry if I am being difficult).
Thanks again for the help!
Distributing Whole Numbers by Volume Percentage Example.xlsx
Last edited by Optimus21; 05-29-2014 at 02:01 PM.
Anyone able to help?
Will this work?
Please Login or Register to view this content.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Thank for your help! Unfortunately, that formula doesn't consistiently work. For example, if I change the number of employees to 1, it returns a 0 value on every row.
Optimus21, to help us out how would you distribute the following:
4 over three in the following percentages 33%, 33% and 33%. It can't be 1 each so one of them has to get 2. Does it matter? Or 6 over 3 in the following percentages 50%, 25% and 25%, it can't be 3, 2 and 2 so one of the 2's will need to be a 1.
Optimus21,
try the attached. Just enter the number in the bordered box and try different percentage distributions down the left column
percentage whole number distribution.xlsx
I may have over complicated it - but here is one using a macro.
spreader simple2.xlsm
Actually this probably works better...
Crooza: Thank you for the effort! I know I am being a pain, but I am not trying to be!. As far as you example goes, if it is tied exactly it doesn't matter where it goes as long as the total adds up. For example, if you have 4 areas and each has 25% volume and only 3 employees, one area wouldn't get an employee.
Unfortunately it looks like the sheet you put together works for most numbers, but not all. For example, put in 3 employees and 5 areas at 20% each and it returns a negative number in D4.
Scottieh: Thank you for the macro! I just need to decipher how to use it now on the backend (macros are not my strong suit).
Is it just not possible to do this without a macro?
Optimus21,
Try changing my round formulas to rounddown. Does that fix it? Sorry on the iPad at present and can't test it.
I just thought it would be fun to write that one. But the key part to what I am doing is a loop to allocate each unit so that isn't something I can do in a formula.Originally Posted by Optimus21
Scottieh: Thank you for the macro! I just need to decipher how to use it now on the backend (macros are not my strong suit).
Is it just not possible to do this without a macro?
So maybe Crooza's strategy is better for you if macros are an issue. This is my take on it reallocating the remainder to each of the largest numbers based on a rank.
in the sheet the extra rank formula is to differentiate between items of the same value (first one gets the unit) column D is step 1 and G3 is the remainder after first step.
Note it is still an approximation (but I think pretty close) and I haven't made it robust to changes in amounts of data.
percentage whole number distribution3.xlsx
Last edited by scottieh; 06-03-2014 at 07:11 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks