If "blank" then don't count in percentage calculation

[mbritt]

I would like to create an If/Then formula where I have a column of data (1 = yes) and (0 = No) where some cells are blank. Meaning there was neither a yes or no answer. I want to only calculate the percentages of the actual answers, not including the blank cells. How would I write a forumla for If cell is blank do not count it ... ? Have I said this correctly?

 

[VoG]

Try like this

=COUNTIF(A1,A10,"Yes")/COUNTA(A1:A10)

 

[Excelestial]

=countif(A1:A100,">""")/counta(A1:A100)....yields the percentage of actual answers out of the entire range.

 

[mbritt]

Hey guys.... neither one of those worked - most likely "user error" on my part .... first column of data is c5..c30 .... I switched c's for your a's.... HELP!!!!!!

 

[VoG]

Sorry, I had a typo. Try

=COUNTIF(C1:C30,"Yes")/COUNTA(C1:C30)

 

[Excelestial]

=countif(C5:C30,">""")/counta(C5:C30)....this is inclusive of Yes AND No answers.

 

[mbritt]

I need some of that Tangeray in your photo!!! Your formula worked but I got "0%" as my answer .... all those 1s and 0s didn't cooperate!!!! I feel so like the blonde I am!!!

Maybe I'm not analyzing the data properly....

 

[VoG]

My results (loads of blanks and a few values)

Excel Workbook
	C	D
5	Yes	0.666667
6
7
8
9	No
10
11
12
13	Yes
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Sheet4

 

[mbritt]

OK, but my data has "1" for yes and "0" for no ... would it be better to change those to yes and no, actual words?

 

[VoG]

Try

=COUNTIF(C1:C30,1)/COUNTA(C1:C30)