LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 110
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Percentage Formula Help cempire5 Excel Discussion (Misc queries) 3 October 3rd 07 03:05 AM
Formula for percentage [email protected] Excel Discussion (Misc queries) 1 September 20th 07 08:41 AM
percentage formula James L Excel Discussion (Misc queries) 8 August 20th 07 07:49 AM
IF formula & Percentage Drummy Excel Discussion (Misc queries) 4 June 5th 06 07:50 AM
Percentage Formula sunderland27 Excel Discussion (Misc queries) 5 April 22nd 06 01:31 AM


All times are GMT +1. The time now is 11:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"