Using MODE

S

sip8316

Is there a way to display some arbitrary word or symbol in a cell if there is
no mode.

I use this to take the mode: =MODE(IF(BV35:BV40<>0,BV35:BV40)) of all non
zero answers, but sometimes there are only one non zero numbersand sometimes
there are multiple non zero numbers but only one of each number.

How do I get it to display " ", or "no mode", etc. Instead of ginving me the
#N/A error in the cell.

Thanks
 
G

galimi

Try the following to test for #VALUE and #N/A

=IF(OR(ISERR(MODE(D9)),ISNA(MODE(D9))),"No Mode",MODE("D9"))
 
S

sip8316

That does get rid of the Error when I put it in,

But when I use that it takes the mode of all the values in the column. I
want to only take the mode of the non zero values in the column like I was
before,

Scott
 
D

Domenic

Try...

=IF(ISNA(MODE(IF(A1:A10<>0,A1:A10))),"No
Mode",MODE(IF(A1:A10<>0,A1:A10)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
S

sip8316

Thanks Domenic,

That worked great

Scott

Domenic said:
Try...

=IF(ISNA(MODE(IF(A1:A10<>0,A1:A10))),"No
Mode",MODE(IF(A1:A10<>0,A1:A10)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
Click to expand...
 
Top