If all cells in a range are N/A, then N/A?
 

HI folks, thanks for the wonderful resource here. I've found a lot of answers
but I had to ask this one ...

I'm working on a spreadsheet that tallies if certain criteria have been met.

Range N15:N31 has three possible answers Y, N or N/A

In cell N32, I tally all the possible answers
=COUNTIF(N15:N31,"Y")+COUNTIF(N15:N31,"N")

In Cell N33 I tally the percentage of answers that are Y
=COUNTIF(N15:N31,"Y")/N32

In Cell N33 I tally the percentage of answers that are N
=COUNTIF(N15:N31,"N")/N32

ISSUE
I've run into the situation of having "div/0" because sometimes all cells in
the range are N/A.

QUESTION
Is there a way to keep the functionality I have but add "if all in range are
N/A, enter N/A"?

Thanks in advance!

If all cells in a range are N/A, then N/A?
 

The only reason you'd get #DIV/0! is if N32 = 0 or is empty.

Try testing N32:

=IF(N32=0,"N/A",COUNTIF(..............))

Biff

"Anastasio Jimenez" wrote in
message ...
HI folks, thanks for the wonderful resource here. I've found a lot of
answers
but I had to ask this one ...

I'm working on a spreadsheet that tallies if certain criteria have been
met.

Range N15:N31 has three possible answers Y, N or N/A

In cell N32, I tally all the possible answers
=COUNTIF(N15:N31,"Y")+COUNTIF(N15:N31,"N")

In Cell N33 I tally the percentage of answers that are Y
=COUNTIF(N15:N31,"Y")/N32

In Cell N33 I tally the percentage of answers that are N
=COUNTIF(N15:N31,"N")/N32

ISSUE
I've run into the situation of having "div/0" because sometimes all cells
in
the range are N/A.

QUESTION
Is there a way to keep the functionality I have but add "if all in range
are
N/A, enter N/A"?

Thanks in advance!

If all cells in a range are N/A, then N/A?
 

Yup, you're absolutely right. Well that fixed it but gave me an error
somewhere else. I'll research that one before I think about posting a
question.

Thanks a lot Biff, I'd been scratching my head over that one. So simple too
....

"Biff" wrote:

The only reason you'd get #DIV/0! is if N32 = 0 or is empty.

Try testing N32:

=IF(N32=0,"N/A",COUNTIF(..............))

Biff

"Anastasio Jimenez" wrote in
message ...
HI folks, thanks for the wonderful resource here. I've found a lot of
answers
but I had to ask this one ...

I'm working on a spreadsheet that tallies if certain criteria have been
met.

Range N15:N31 has three possible answers Y, N or N/A

In cell N32, I tally all the possible answers
=COUNTIF(N15:N31,"Y")+COUNTIF(N15:N31,"N")

In Cell N33 I tally the percentage of answers that are Y
=COUNTIF(N15:N31,"Y")/N32

In Cell N33 I tally the percentage of answers that are N
=COUNTIF(N15:N31,"N")/N32

ISSUE
I've run into the situation of having "div/0" because sometimes all cells
in
the range are N/A.

QUESTION
Is there a way to keep the functionality I have but add "if all in range
are
N/A, enter N/A"?

Thanks in advance!

If all cells in a range are N/A, then N/A?
 

You're welcome. Let us know if you need further assistance.

Biff

"Anastasio Jimenez" wrote in
message ...
Yup, you're absolutely right. Well that fixed it but gave me an error
somewhere else. I'll research that one before I think about posting a
question.

Thanks a lot Biff, I'd been scratching my head over that one. So simple
too
...

"Biff" wrote:

The only reason you'd get #DIV/0! is if N32 = 0 or is empty.

Try testing N32:

=IF(N32=0,"N/A",COUNTIF(..............))

Biff

"Anastasio Jimenez" wrote in
message ...
HI folks, thanks for the wonderful resource here. I've found a lot of
answers
but I had to ask this one ...

I'm working on a spreadsheet that tallies if certain criteria have been
met.

Range N15:N31 has three possible answers Y, N or N/A

In cell N32, I tally all the possible answers
=COUNTIF(N15:N31,"Y")+COUNTIF(N15:N31,"N")

In Cell N33 I tally the percentage of answers that are Y
=COUNTIF(N15:N31,"Y")/N32

In Cell N33 I tally the percentage of answers that are N
=COUNTIF(N15:N31,"N")/N32

ISSUE
I've run into the situation of having "div/0" because sometimes all
cells
in
the range are N/A.

QUESTION
Is there a way to keep the functionality I have but add "if all in
range
are
N/A, enter N/A"?

Thanks in advance!