Percentage Formula

[avistasys]

Hello,

I am unable to work out or find a solution online for the following sheet.

I have a number of tasks to complete which are recorded as YES or No, as not all tasks are required for certain products these are recorded as NA. (drop downs)

The problem I face is to adjust the overall percentage of the sheet when NA is recorded, currently these affect the percent compliance if used.

What changes do I need to make for the formula please. I have attached an image.

Many thanks in advance.

=COUNTIF(E2:E13,"Yes")
=COUNTIF(E2:E13,"No")
=COUNTIF(E2:E13,"NA")

=SUM(E14/(B13-E16))

Kind regards
Avistasys

 

[Joe4]

If you simply want the percentage of "Yes" votes, based on all the non-NA records, it would just be:
"Yes Votes" divided by ("Yes Votes" + "No Votes"), or:
=COUNTIF(E2:E13,"Yes")/(COUNTIF(E2:E13,"Yes")+COUNTIF(E2:E13,"No"))

 

[Fluff]

Hi & welcome to MrExcel.
How about
=E14/(E14+E15)

 

[Joe4]

How about
=E14/(E14+E15)

I should have looked at the sheet!
Though our answers will both give the correct results, since they already calculated those COUNTIF amounts in those cells, it is a little easier to do it that way, then it is to recalculate those amounts again.

 

[Fluff]

I'm not even sure if we have understood the OP, as the formula they supplied gives the same result as our suggestions.

 

[avistasys]

Hi & welcome to MrExcel.
How about
=E14/(E14+E15)

Thank you so much, I shall try it and come back to you.

 

[avistasys]

Hello,

I am unable to work out or find a solution online for the following sheet.

I have a number of tasks to complete which are recorded as YES or No, as not all tasks are required for certain products these are recorded as NA. (drop downs)

The problem I face is to adjust the overall percentage of the sheet when NA is recorded, currently these affect the percent compliance if used.

What changes do I need to make for the formula please. I have attached an image.

Many thanks in advance.

=COUNTIF(E2:E13,"Yes")
=COUNTIF(E2:E13,"No")
=COUNTIF(E2:E13,"NA")

=SUM(E14/(B13-E16))

Kind regards
Avistasys

Hi gents,

Thank you so much for your speedy responses very much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[avistasys]

Hi & welcome to MrExcel.
How about
=E14/(E14+E15)

Glad we could help & thanks for the feedback.

Hello Fluff,

Unfortunately I just tried the formula and it is not working as required, I shall try to explain what I am seeking.

The objective is to undertake tasks and score against compliance, but NA is affecting the compliance percentage...

The total number of tasks is 12 which remains constant, therefore 100% if the answer to all tasks is YES. This percentage decreases when NO is answered.
If NA is answered the percentage should not be affected. As it is now, if NA is answered that cell affects the percentage. ( ie say one NA cell should adjust for 11 tasks not 12)

Any assistance would be appreciated

Kind regards

 

[Fluff]

In that case I still don't understand what you want.
All the formula supplied do what you describe.

+Fluff New.xlsm
	A	B	C	D	E	F	G
1
2		1			Yes	Yes	Yes
3		2			Yes	Yes	Yes
4		3			Yes	Yes	Yes
5		4			Yes	Yes	Yes
6		5			Yes	Yes	Yes
7		6			Yes	No	NA
8		7			Yes	No	NA
9		8			Yes	No	NA
10		9			Yes	No	NA
11		10			Yes	No	NA
12		11			NA	NA	NA
13		12			NA	NA	NA
14					10	5	5
15					0	5	0
16					2	2	7
17					100%	50%	100%
18					100%	50%	100%
19					100%	50%	100%
List

Cell Formulas
Range	Formula
E14:G14	E14	=COUNTIF(E2:E13,"Yes")
E15:G15	E15	=COUNTIF(E2:E13,"No")
E16:G16	E16	=COUNTIF(E2:E13,"NA")
E17:G17	E17	=E14/(E14+E15)
E18:G18	E18	=SUM(E14/($B13-E16))
E19:G19	E19	=COUNTIF(E2:E13,"Yes")/(COUNTIF(E2:E13,"Yes")+COUNTIF(E2:E13,"No"))