Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Always Sum a list of %'s to 100%
How do I always get a list of percentages to sum to 100%?
For example, the list below all sums to 100%. I want to develop a formula so that if I change 6% to 7%, the rest of the numbers will change so that they all equal 100% again. So basically, the 1% difference needs to be redistrubted evenly to the other percentages automatically when I change the 6% to 7%. I want this formula to hold all the way through--if I want to change the 59% to 30% and so on, I still want them to sum to 100%. 59.000% 15.000% 5.000% 2.000% 6.000% 4.000% 2.000% 5.000% 2.000% Help is appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Always Sum a list of %'s to 100%
AMH -- Try something like this:
A B 1 59 =a1/a10 2 15 =a2/a10 3 5 etc 4 2 5 6 6 4 7 2 8 5 9 2 10 =sum(A1:A9) Haven't tried it, but should do the trick. HTH "AMH" wrote: How do I always get a list of percentages to sum to 100%? For example, the list below all sums to 100%. I want to develop a formula so that if I change 6% to 7%, the rest of the numbers will change so that they all equal 100% again. So basically, the 1% difference needs to be redistrubted evenly to the other percentages automatically when I change the 6% to 7%. I want this formula to hold all the way through--if I want to change the 59% to 30% and so on, I still want them to sum to 100%. 59.000% 15.000% 5.000% 2.000% 6.000% 4.000% 2.000% 5.000% 2.000% Help is appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Always Sum a list of %'s to 100%
Solver is a very powerful tool.
Read this article; this should give you what you want: http://www.uic.edu/classes/fin/fin512/Data/solver.pdf Regards, Ryan-- -- RyGuy "pdberger" wrote: AMH -- Try something like this: A B 1 59 =a1/a10 2 15 =a2/a10 3 5 etc 4 2 5 6 6 4 7 2 8 5 9 2 10 =sum(A1:A9) Haven't tried it, but should do the trick. HTH "AMH" wrote: How do I always get a list of percentages to sum to 100%? For example, the list below all sums to 100%. I want to develop a formula so that if I change 6% to 7%, the rest of the numbers will change so that they all equal 100% again. So basically, the 1% difference needs to be redistrubted evenly to the other percentages automatically when I change the 6% to 7%. I want this formula to hold all the way through--if I want to change the 59% to 30% and so on, I still want them to sum to 100%. 59.000% 15.000% 5.000% 2.000% 6.000% 4.000% 2.000% 5.000% 2.000% Help is appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dropdown List - list item endings not visible if column too narrow | Excel Discussion (Misc queries) | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions | |||
sort list of players by team from player list on separate sheet | Excel Worksheet Functions |