Percentage Formula Help

[Steph]

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

 

[Sandy Mann]

Either I'm not understanding you or your figures are wrong:

B1: =IF(A1=MAX($A$1:$A$4),1-SUM(B2:B4),A1/$A$5)
B2: =IF(A2=MAX($A$1:$A$4),1-SUM(B1,B3:B4),A2/$A$5)
B3: =IF(A3=MAX($A$1:$A$4),1-SUM(B1:B2,B4),A3/$A$5)
B4: =IF(A4=MAX($A$1:$A$4),1-SUM(B1:B3),A4/$A$5)

A5: =SUM(A1:A4)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk