S

#### Steph

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