Need Formula

[Guest]

Hello, I hope someone can help me. I am working on a spreadsheet in which I
want to average a column of cells with the letter "Y", but not average the
cells (in the same range) with an "NA". I would also like the average total
be displayed as a percentage.

For Example:
A B C
1 Y
2 NA
3 Y
4 Y
5
6 NA
7 Y

In this example I want to average A1:A7, but return a percentage of the
"Y's" and blanks and not include the "NA's." I know the answer will be 80%
in this example, but I don't know how to express that in a formula.

Thanks in advance,
Ryan

 

[N Harkawat]

=COUNTIF(A1:A7,"Y")/COUNTIF(A1:A7,"<>NA")
and format as percentage

 

[Guest]

=(rows(A1:A7)-countif(A1:A7,"NA"))/rows(A1:A7)

Assumes you want to exclude ONLY the NAs. But it's not going to be 80% in
your example. 5/7 is NOT 80%

 

[Guest]

if you know you want calculations on 7 rows
one method would be
=1-countif(A1:A7,"NA")/7
and format as a percentage

 

[Biff]

Hi!

return a percentage of the "Y's" and blanks

Are the blank cells truely EMPTY or do they contain formulas that return
formula blanks ("") ?

Can you explain how you get 80% as your result?

Counting "Y's" and blanks as a percentage of the range size results in
71.4286%

Biff

 

[Guest]

The blank Cells are empty...no formulas.

In the average computation, I only want to average the "Y's" and the blanks.
The NA's will not be a part of the average. So in my example, I am only
averaging 5 cells, not 7 (because 2 of the cells contain "NA").

Hope that clarifies.

Thanks again.

Ryan

 

[Biff]

I like Duke's formula but here's an alternative:

=(COUNTIF(A1:A7,"Y")+COUNTBLANK(A1:A7))/ROWS(A1:A7)

Format as PERCENTAGE

Biff

 

[Guest]

=COUNTIF($A$1:$A$7,"Y")/(COUNTIF($A$1:$A$7,"")+COUNTIF($A$1:$A$7,"Y"))

Hope, the above formula will work for your problem.

Thanks,

Manish Bajpai

 

[Guest]

I got the formula to work. Thank you all so much for your help.