ExcelBanter - View Single Post

John

Yep, now we're rolln'!

Thanks!

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xTextMode.xls 14kb

http://cjoint.com/?dvw31x1fJz

The file shows 2 different methods. One in which there are no empty cells
within the range and one where the range does have empty cells.

--
Biff
Microsoft Excel MVP

"John" wrote in message
...
Not having much luck. Here is what I am entering.

=INDEX(Data!M2:M1000,MODE(MATCH(Data!M2:M1000,Data !M2:M1000,0)+{0,0}))

I'm setting it as an array and all I get is #N/A

What am I doing wrong?

"T. Valko" wrote:

One way...

Assume data is in the range A2:A7 and there are no empty cells within the
range.

Enter this formula in C2:

=INDEX(A2:A7,MODE(MATCH(A2:A7,A2:A7,0)+{0,0}))

Enter this array formula** in C3 and copy down to C4:

=INDEX(A$2:A$7,MODE(IF(COUNTIF(C$2:C2,A$2:A$7)=0,M ATCH(A$2:A$7,A$2:A$7,0)+{0,0})))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"John" wrote in message
...
I am trying to write a formula that will look at a column of data and
return
the 1st, 2nd, and 3rd largest values. My major issue is that the values
are
text. Example:

Term Reasons
Failed Testing
Failed Testing
Failed Tesing
Fingerprinting
Failed BG Check
Failed BG Check

I need to return the data as...

#1 Term Reason = Failed Testing
#2 Term Reason = Failed BG Check
#3 Term Reason = Fingerprinting

Using Excel 2003

Thanks!