Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Percentage Formula Help
I'm trying to create a formula to calculate percentages that does the
following: - Takes a rounded percentage of the total for all values except the maximum. - For the maximum, it sums the rounded percentages for all other values and subtracts the total from one. For example, the output with data in A1:A4 would look like the following (A5 is the total of A1:A4): A1= 7 B1= 18% (7/30) A2= 6 B2= 20% (6/30) A3= 8 B3= 21% (8/30) A4= 9 B4= 24% (1 - .70) A5=38 B5=100% Cell B4 is the total of B1:B3. If the data were changed and the maximum is now in A2, the output should look like this: A1= 7 B1= 23% (7/38) A2=14 B2= 37% (1 - .63) A3= 8 B3= 27% (8/38) A4= 9 B4= 30% (9/38) A5=38 B5=100% Cell B2 is the total of B1, B3, and B4. The reason for the request is that the percentages of the data that I'm working with, when rounded, do not always total to 100%. Sometimes they total 101% and other times 99%. The formula would adjust the maximum to "plug" the total to 100% if needed. Assuming A1:A4 is named "Data" and A5 is named "Total", the following formula copied into cells B1:B4 works most of the time. However, it fails if two or more numbers make up the maximum. =IF(A1/Total=MAX(Data)/Total,1-SUMPRODUCT(((ROUND((Data)/Total,2))<MAX(ROUND((Data)/Total,2)))*(ROUND((Data)/Total,2))),ROUND(A1/Total,2)) Thank you for any help that can be provided. Steph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage Formula Help | Excel Discussion (Misc queries) | |||
Formula for percentage | Excel Discussion (Misc queries) | |||
percentage formula | Excel Discussion (Misc queries) | |||
IF formula & Percentage | Excel Discussion (Misc queries) | |||
Percentage Formula | Excel Discussion (Misc queries) |