Using MODE
 

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

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

=IF(OR(ISERR(MODE(D9)),ISNA(MODE(D9))),"No Mode",MODE("D9"))
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"sip8316" wrote:

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

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

"galimi" wrote:

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

=IF(OR(ISERR(MODE(D9)),ISNA(MODE(D9))),"No Mode",MODE("D9"))
--
http://HelpExcel.com
1-888-INGENIO
1-888-464-3646
x0197758


"sip8316" wrote:

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

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!

In article ,
"sip8316" wrote:

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

Thanks Domenic,

That worked great

Scott

"Domenic" wrote:

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!

In article ,
"sip8316" wrote:

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