Using Mode to find the most popular values. Not working.

I need to find the most popular medication codes in a range of data.

I'm trying to use this formula:
=IF(COUNTA(S$1:AI$1306)<10,0,INDEX(S$1:AI$1306,MODE(MATCH(S1$1:AI$1306,S$1:AI$1306,0)+{0,0})))

which, up until now, when used as an array, has been listing the most popular values within a column in succession

but it's giving me an #N/A value. I have three hypotheses:

1) It's because #N/A values exist in that range
So I did a find and replace to change all the #N/A values to a fake med code.
That didn't fix the problem
2) It's because there's these "." to indicate blank data (no med codes) in this range.
So, I did a find and replace to change all the "." to a fake med code, but that didn't fix the problem either.
3) It's because I'm using this formula on a range of data, rather than simply a column.
I don't know how plausible this is. And It'd be a lot of work to test this one.

So I thought I'd throw it out there, with the hope that you guys might have something to suggest. All suggestions are welcome and appreciated.

Thank you.

Originally Posted by adhamija

=IF(COUNTA(S$1:AI$1306)<10,0,INDEX(S$1:AI$1306,MODE(MATCH(S1$1:AI$1306,S$1:AI$1306,0)+{0,0})))

up until now, when used as an array, has been listing the most popular values within a column in succession

I confess I'm slightly confused by the above assertion given MATCH works with vectors rather than matrices.

Could you post a sample to illustrate what you're trying to achieve ?

A formula based approach will be very inefficient and most likely Volatile
given use of XL2003 the options open to you are further restricted

I would suggest you consider using a User Defined Function (VBA) - is that viable in your environment ?

The below is adapted from a post on a not too dissimilar questions asked a week or so ago: http://www.excelforum.com/excel-work...ing-words.html

Though the above thread and yours vary the code below should suffice (if a little over engineered for this particular query)

Please Login or Register  to view this content.

Attached is a working example - modifying inputs (Yellow) should cause results (Green) to update accordingly.

Medium Security expected - "Enable Macros" on prompt - you can modify the output as necessary of course.

Other than updating your profile to indicate use of 2008 you mean

Yes, no VBA support in 2008 unfortunately: remedied in 2011 thankfully.

Formula wise - given use of XL2008 I will have a think... whatever people come up with you should note it won't be trivial and will be hideously inefficient unless you consider use of helper/intermediate matrix (COUNTIF etc)

If you are able to revert to a vector you should - you can then just use a Pivot Table.

In terms of single cell formulae / no helpers - attached is one approach.

As mentioned / repeated previously the attached approach is is non-trivial, inefficient & volatile.

The file in my prior post works ok for me I'm afraid - data is in Col Q onwards (only 1 sheet in file)

Not sure if 2008 has Pivot Table Wizard or not. I normalized the data using the wizard and then created the pivot table. See attached. (this is a new trick I just learned )