Calculate percentage based on number of cells with a value

[Charles_]

Hello,

Apologies if this is repeated, I've tried searching for an answer but have not found anything.

I am looking for a way to calculate a percentage based on the number of cells with a value

example

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sales person 1 [/TD]
[TD]Sales person 2[/TD]
[TD]Sales person 3[/TD]
[TD]Sales person 1 %[/TD]
[TD]Sales person 2 %[/TD]
[TD]Sales person 3%[/TD]
[/TR]
[TR]
[TD]100 items[/TD]
[TD]150 items[/TD]
[TD][/TD]
[TD]=average of number of cells with data, in this case only salespersons 1 and 2

In this case = 40% [/TD]
[TD]=average of number of cells with data, in this case only salespersons 1 and 2

In this case = 60% [/TD]
[TD]=determine that no there is no data for salesperson #3 , therefore leave blank[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[mikerickson]

=SUMIF(A1:C1,"Salesperson 1",A2:C2)/SUM(A2:C2) should return the values that you want.

You can format to show a 0 result as blank.

 

[Gerald Higgins]

Hi, how did you arrive at the 40% and 60% results ?

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Sales person 1	Sales person 2	Sales person 3	Sales person 1 %	Sales person 2 %	Sales person 3%
2	100	150		0.4	0.6	0
Master

Cell Formulas
Range	Formula
D2	=A2/AGGREGATE(9,6,$A2:$C2)
E2	=B2/AGGREGATE(9,6,$A2:$C2)
F2	=C2/AGGREGATE(9,6,$A2:$C2)

 

[jtakw]

Hi,

How about:

Book1
	A	B	C	D	E	F
1	Sales person 1	Sales person 2	Sales person 3	Sales person 1 %	Sales person 2 %	Sales person 3%
2	100	150		0.4	0.6
Sheet367

Cell Formulas
Range	Formula
D2	=IF(A2,A2/SUM($A2:$C2),"")

Formula copied across.

 

[Charles_]

Hi Fluff,

Thank you but I am getting a result of 0 using the formula. I neglected the fact there are other columns in between the sales person data, trying to simplify it but appreciate this might have been counterproductive apologies.

I've tried using the below formula in red to no avail:

 

[Fluff]

I've had to edit your post as it destroyed the thread.
Is this what your data looks like

Excel 2013/2016
	B	C	D	E	F	G	H	I	J
1	Sales person 1	Other data	Sales person 2	Other data	Sales person 3	Other data	Sales person 1 %	Sales person 2 %	Sales person 3%
2	100	abc	150	abc		abc	0.4	0.6	#VALUE!
Sheet2

Cell Formulas
Range	Formula
H2	=B2/AGGREGATE(9,6,B2,D2,F2)
I2	=D2/AGGREGATE(9,6,B2,D2,F2)
J2	=E2/AGGREGATE(9,6,B2,D2,F2)

 

[Charles_]

Thank you and apologies not sure what happened with my post!

Still not working thou, I am getting a '1' as a result when it shouldn't.

Does the fact the values in cells B2, D2 and E2 are calculations based on other formulas matter?

Thank you

 

[Fluff]

What do you get if you use jtakw's formula like
=IF(B2,B2/SUM($A2:$G2),"")
Also what is the cell format for H2:J2

 

[Charles_]

Never mind me Fluff, I got it to work thank you!