mode of range containing some empty cells?

[nicknicknickandnick]

Is there a way to apply the mode function to a range of cells that may sometimes contain cells with non-numeric text or are empty? When I try to do that, I get the #N/A error. I only want the mode for the cells within the range that contain numbers, and have other cells of the range ignored.

 

[vconfused]

In your testing, is it possible that you do not have a single number that exists more than once?

If I have 1 in A1, nothing in A2, a in A3, and 1 in A4, and in B1 type in =mode(A1:A4), I get 1. If I replace the 1 in A1 with 2, then I get #N/A since there is no duplicate number.

Can you replicate this?

 

[nicknicknickandnick]

OK, that actually works, thanks. My problem is something different than I thought, it seems. I'm trying to get the mode based on the entire contents of one sheet to show up on another sheet. It doesn't work when I reference the whole sheet (clicking the upper left thing), but it does work when I select a part of the sheet. The error I get is #NUM!. I'd like to reference the entire sheet.

 

[vconfused]

Looks like a limitation of the program. I can get it to do all but one row:
=MODE(Sheet1!1:65535)
or
=MODE(Sheet1!2:65536)

It does not seem to work with an entire column. I.e. =MODE(A:A) yields #NUM!.

It seems you can get around this limitation by splitting the range up, for instance:
=MODE(Sheet1!1:2,Sheet1!3:65536)

 

[nicknicknickandnick]

Interesting, thanks. I guess it's just as well that it isn't easy to do the whole sheet -- it slows things down like crazy.