I'm working with lists of numbers that should return bi and multi-modal
results when analyzed. MODE() only returns the first mode, and then stops.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

Thanks,
Joe Dolsak

Let A2:A10 house a sample of numeric data...

C2:

=MODE(A2:A10)

C3:

=MODE(IF(ISNUMBER(MATCH($A$2:$A$10,$C$2:C2,0)),"", $A$2:$A$10))

which must be confirmed with control+shift+enter then copied down.

Jdolsak wrote:
I'm working with lists of numbers that should return bi and multi-modal
results when analyzed. MODE() only returns the first mode, and then stops.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

Thanks,
Joe Dolsak

The problem with that equation is that it not only captures the modes if you
copy it down, but the next most frequent and so on (which is not to say that
it isn't very, very, very useful).

So, once you copy the formula down, how do you determine which numbers are
modes and which are merely second, third, fourth...most frequent without
using a histogram?

"Aladin Akyurek" wrote:

Let A2:A10 house a sample of numeric data...

C2:

=MODE(A2:A10)

C3:

=MODE(IF(ISNUMBER(MATCH($A$2:$A$10,$C$2:C2,0)),"", $A$2:$A$10))

which must be confirmed with control+shift+enter then copied down.

Jdolsak wrote:
I'm working with lists of numbers that should return bi and multi-modal
results when analyzed. MODE() only returns the first mode, and then stops.

Is there a way to get MODE() to return these results? Or is there another
method to get correct results for the modes of bi- and multi-modal data?

Thanks,
Joe Dolsak

You could add this formula to D2:

=COUNTIF(A$2:A$10,C2)

and copy it down to give you a count of each of the numbers in C.
Doesn't answer your question, but useful nonetheless.

Pete

Right and thank you, but is there anyway to make the formula stop once the
frequency lowers from the mode, so I don't have to do that?

I didn't/don't mean to come off as sarcastic, either; a formula that returns
a frequency-ranked set of data is pretty neat in my naive little book.

"Pete_UK" wrote:

You could add this formula to D2:

=COUNTIF(A$2:A$10,C2)

and copy it down to give you a count of each of the numbers in C.
Doesn't answer your question, but useful nonetheless.

Pete

Another problem with the formula is that if any of your values are zero,
something gets tripped up. Even if zero has a lower frequency, I'm finding
it stuck in the resulting list of numbers in strange places.

"Rothman" wrote:

Right and thank you, but is there anyway to make the formula stop once the
frequency lowers from the mode, so I don't have to do that?

I didn't/don't mean to come off as sarcastic, either; a formula that returns
a frequency-ranked set of data is pretty neat in my naive little book.

"Pete_UK" wrote:

You could add this formula to D2:

=COUNTIF(A$2:A$10,C2)

and copy it down to give you a count of each of the numbers in C.
Doesn't answer your question, but useful nonetheless.

Pete