Spread of the percentage

Could you provide a little context, explain what you're calculating?

In AH10 and copy across,

=70% * AD10 / SUM($AD10:$AF10) + 10%

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

10

x

x

x

x

x

x

x

x

x

x

x

x

x

6

7

0

42%

48%

10%

Originally Posted by Tresfjording

All values must be rounded up or down to the nearest ten.

How does that reconcile with "How can I get the percentage to be exact?"

And you formula ends in a 0 in all three cells.

Not if you format them as Percent.

Have you tested it in other combinations by entering x's in each players cells?

	AD	AE	AF	AG	AH	AI	AJ
16	1	5	1		20%	60%	20%
17	7	4	7		37%	26%	37%
18	0	6	2		10%	63%	28%
19	7	0	3		59%	10%	31%
20	5	5	6		32%	32%	36%
21	4	5	2		35%	42%	23%
22	4	4	3		35%	35%	29%
23	6	3	3		45%	28%	28%
24	3	6	6		24%	38%	38%
25	4	1	6		35%	16%	48%

What result does that give for {5,5,0}?

What should it give?

This proposal employs four helper columns which may be moved and/or hidden for aesthetic purposes.
1. Cell AL10 is populated using: =MATCH(MODE.SNGL(AD10:AF10),AD10:AF10,0)
2. Cells AM10:AO10 are populated using: =IFERROR(IF($AL10=COLUMNS($AM10:AM10),SUM(AD10,1),AD10),AD10)
3. Cells AD10:AF10 are populated using: =IF($A10="","",IF(COUNTA(B10,F10,J10,N10,R10,V10,Z10)=0,1,COUNTA(B10,F10,J10,N10,R10,V10,Z10)))
4. Cells AH10:AJ10 are populated using: =ROUND(AM10/SUM($AM10:$AO10),1)
Let us know if you have any questions.

Hi ...

This seems like a good solution, but when changing AF14 to a zero, the result in cell AJ14 is 0%, even though it should never be less than 10%.

Attachment 782558

Tresfjording

Try modifying the formula in AM10:AO10 so that it reads:

Formula:

Please Login or Register  to view this content.

By the way, selecting attachment 782558 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
In the future please utilize the information in the banner at the top of the page to attach and Excel file.
Let us know if you have any questions.