Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
I have 5 #s. each represents a percentage of the total. how can i limit a
number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 0.005552647 228 0.069816829 760 0.232627383 1,177 0.360402951 1,083 0.331600189 3,266 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
Wizard475 wrote:
I have 5 #s. each represents a percentage of the total. how can i limit a number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 0.005552647 228 0.069816829 760 0.232627383 1,177 0.360402951 1,083 0.331600189 3,266 With your numbers in A1:A5, sorted descending B1=IF(A1SUM($A$1:$A$5)*0.3,INT(SUM($A$1:$A$5)*0.3 ),A1) B2=IF((A2+SUM($A$1:A1)-SUM($B$1:B1))SUM($A$1:$A$5)*0.3,INT(SUM($A$1:$A$5 )*0.3),(A2+SUM($A$1:A1)-SUM($B$1:B1))) Fill B2 down to B5 If that's not the result you desire, then more information is needed. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
thanks for the response. what if i am unable to sort the values?
"Glenn" wrote: Wizard475 wrote: I have 5 #s. each represents a percentage of the total. how can i limit a number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 0.005552647 228 0.069816829 760 0.232627383 1,177 0.360402951 1,083 0.331600189 3,266 With your numbers in A1:A5, sorted descending B1=IF(A1SUM($A$1:$A$5)*0.3,INT(SUM($A$1:$A$5)*0.3 ),A1) B2=IF((A2+SUM($A$1:A1)-SUM($B$1:B1))SUM($A$1:$A$5)*0.3,INT(SUM($A$1:$A$5 )*0.3),(A2+SUM($A$1:A1)-SUM($B$1:B1))) Fill B2 down to B5 If that's not the result you desire, then more information is needed. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
On Jun 27, 1:13*pm, Wizard475
wrote: I have 5 #s. *each represents a percentage of the total. *how can i limit a number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 * * *0.005552647 228 * * 0.069816829 760 * * 0.232627383 1,177 * 0.360402951 1,083 * 0.331600189 3,266 * If the first column of figures is in A2:A6 and the total is in A8, but the following in B2 (or any parallel column, changing the references to column B) and copy down, making sure that B1 is blank or text: =MIN(30%*$A$8, SUMPRODUCT((ROW(A2)=ROW($A$2:$A$6)) *LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A $1))) -SUM($B$1:B1)) That sums the largest N numbers, where is N is the relative row number in column B; subtracts the sum of the previous N-1 derived values; and limits the result to 30% of the total. Note: An alternative expression of the SUMPRODUCT is: SUMPRODUCT(--(ROW(A2)=ROW($A$2:$A$6)), LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1))) I remember some discussion of the relative merits; but I don't remember what they are. In this circumstance, both forms seem to work. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
PS...
On Jun 27, 4:02 pm, I wrote: *LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1)) Sorry about the typos and text alignment in the previous posting. I hope it is clear. I just wanted to add.... Since ROW($A$1) always returns 1, you might wonder: why not simply write 1 instead of ROW($A$1)? The answer is: it is intended to show the derivation of that factor. If the table were in A17:A21, you would write ROW($A$16) -- or simply 16, if perfer. On Jun 27, 4:02*pm, joeu2004 wrote: On Jun 27, 1:13*pm, Wizard475 wrote: I have 5 #s. *each represents a percentage of the total. *how can i limit a number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 * * *0.005552647 228 * * 0.069816829 760 * * 0.232627383 1,177 * 0.360402951 1,083 * 0.331600189 3,266 * If the first column of figures is in A2:A6 and the total is in A8, but the following in B2 (or any parallel column, changing the references to column B) and copy down, making sure that B1 is blank or text: =MIN(30%*$A$8, * * * * *SUMPRODUCT((ROW(A2)=ROW($A$2:$A$6)) * * * * * * * * * * * * * * * * *LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A $1))) * * * * *-SUM($B$1:B1)) That sums the largest N numbers, where is N is the relative row number in column B; subtracts the sum of the previous N-1 derived values; and limits the result to 30% of the total. Note: *An alternative expression of the SUMPRODUCT is: SUMPRODUCT(--(ROW(A2)=ROW($A$2:$A$6)), * * * * * * * * * * * * LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1))) I remember some discussion of the relative merits; but I don't remember what they are. *In this circumstance, both forms seem to work. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
Errata....
Although I believe the following suggestions meets your requirements as stated, I suspect it does not truly meet your needs. Note that the results in B2:B6 are not in the same order as the original data. That is, B2 does not correspond to the figure in A2 -- ergo, it does not correspond to any descriptive text in a column parallel to A2:A6 (e.g. category names). If you do indeed need the results to be in the same order, I don't know if my formula is a good place to start, or if there is a better approach altogether. Sorry, but I don't have any more time to think about it. On Jun 27, 4:02*pm, joeu2004 wrote: On Jun 27, 1:13*pm, Wizard475 wrote: I have 5 #s. *each represents a percentage of the total. *how can i limit a number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 * * *0.005552647 228 * * 0.069816829 760 * * 0.232627383 1,177 * 0.360402951 1,083 * 0.331600189 3,266 * If the first column of figures is in A2:A6 and the total is in A8, but the following in B2 (or any parallel column, changing the references to column B) and copy down, making sure that B1 is blank or text: =MIN(30%*$A$8, * * * * *SUMPRODUCT((ROW(A2)=ROW($A$2:$A$6)) * * * * * * * * * * * * * * * * *LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A $1))) * * * * *-SUM($B$1:B1)) That sums the largest N numbers, where is N is the relative row number in column B; subtracts the sum of the previous N-1 derived values; and limits the result to 30% of the total. Note: *An alternative expression of the SUMPRODUCT is: SUMPRODUCT(--(ROW(A2)=ROW($A$2:$A$6)), * * * * * * * * * * * * LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1))) I remember some discussion of the relative merits; but I don't remember what they are. *In this circumstance, both forms seem to work. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Limited value based on percentage of total
thanks for the help, i will give it a test.
"joeu2004" wrote: Errata.... Although I believe the following suggestions meets your requirements as stated, I suspect it does not truly meet your needs. Note that the results in B2:B6 are not in the same order as the original data. That is, B2 does not correspond to the figure in A2 -- ergo, it does not correspond to any descriptive text in a column parallel to A2:A6 (e.g. category names). If you do indeed need the results to be in the same order, I don't know if my formula is a good place to start, or if there is a better approach altogether. Sorry, but I don't have any more time to think about it. On Jun 27, 4:02 pm, joeu2004 wrote: On Jun 27, 1:13 pm, Wizard475 wrote: I have 5 #s. each represents a percentage of the total. how can i limit a number's percentage @ 30% and have what is left move to the remaining numbers, all the while no one number can be greatter than 30% of the total. 18 0.005552647 228 0.069816829 760 0.232627383 1,177 0.360402951 1,083 0.331600189 3,266 If the first column of figures is in A2:A6 and the total is in A8, but the following in B2 (or any parallel column, changing the references to column B) and copy down, making sure that B1 is blank or text: =MIN(30%*$A$8, SUMPRODUCT((ROW(A2)=ROW($A$2:$A$6)) *LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A $1))) -SUM($B$1:B1)) That sums the largest N numbers, where is N is the relative row number in column B; subtracts the sum of the previous N-1 derived values; and limits the result to 30% of the total. Note: An alternative expression of the SUMPRODUCT is: SUMPRODUCT(--(ROW(A2)=ROW($A$2:$A$6)), LARGE($A$2:$A$6,ROW($A$2:$A$6)-ROW($A$1))) I remember some discussion of the relative merits; but I don't remember what they are. In this circumstance, both forms seem to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add column for percentage o total | Excel Discussion (Misc queries) | |||
Percentage total may not exceed 100 | Excel Discussion (Misc queries) | |||
Total Revenue Percentage | Excel Discussion (Misc queries) | |||
Percentage Discount Total | Excel Discussion (Misc queries) | |||
a number as a percentage out of a total | Excel Worksheet Functions |