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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Anastasio Jimenez - 10 Nov 2006 19:18 GMT

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!

Reply to this Message

Biff - 10 Nov 2006 19:32 GMT

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

> HI folks, thanks for the wonderful resource here. I've found a lot of
> answers
[quoted text clipped - 25 lines]
>
> Thanks in advance!

Reply to this Message

Anastasio Jimenez - 10 Nov 2006 19:58 GMT

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
...

> The only reason you'd get #DIV/0! is if N32 = 0 or is empty.
>
[quoted text clipped - 33 lines]
> >
> > Thanks in advance!

Reply to this Message

Biff - 10 Nov 2006 21:23 GMT

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

Biff

> 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
[quoted text clipped - 43 lines]
>> >
>> > Thanks in advance!

Reply to this Message