Multiple Mode Formula Malfunction. Please Help?

**pfallonj** · 09-13-2012, 03:53 PM

I'm trying to calculate the nth mode of an array. Here is the formula:

I can get this formula to work when I create a random list of numbers in A1:A23 (for example) in a blank excel sheet, but when I try to run the above formula for a column in an exported table, I keep getting a #REF error...Help?

The Formula:

=MATCH(MATCH(LARGE(FREQUENCY(Q8:Q338,ROW(INDIRECT(MIN(Q8:Q338)&":"&MAX(Q8:Q338)))),2),FREQUENCY(Q8:Q338,ROW(INDIRECT(MIN(Q8:Q338)&":"&MAX(Q8:Q338)))),0),ROW(INDIRECT(MIN(Q8:Q338)&":"&MAX(Q8:Q338))),0)

The "2" in the middle of the first line denotes the 2nd most frequent number. You may substitute any other number or a cell reference containing the number n for the nth most frequent.
This is an array formula and, after you type or paste it into the cell, hold down <ctrl><shift> while hitting <enter> in order to *array-enter*. XL will place braces {...} around the formula.

Sheet 1 is where the formula works.

Sheet 2 is the relevant part of what I've pulled, and where it isn't working.

**Ace_XL** · 09-13-2012, 04:00 PM

The error is on account of '0' values in your range of data, which is why the INDIRECT() will not execute, meanwhile will work on an alternative solution

**MarvinP** · 09-13-2012, 04:07 PM

Hi pfallonj,

Why not use Count instead of Mode in your table. See a Pivot Table solution where you can show only the top 5 and sort them from large to small. No formulas needed, just a little pivot table drag and drop.

See the attached... I think you got a bad answer on sheet 2 because you didn't have values all the way down in your column.

**pfallonj** · 09-13-2012, 04:39 PM

MarvinP,

What on earth did you do?

I've only used Excel intermittently over the years, and pivot tables and all other things useful in excel are a bit fuzzy for me. I've had to piece things together slowly as I go along, and scower forums for things I can try to use.

Mind walking me through what you did?

**MarvinP** · 09-13-2012, 04:48 PM

Hi,

See if this screencast helsp
http://screencast.com/t/Marf0LcQBMX

On your sheet 2 you will need to do a similar thing after inserting a blank row where your data stops.