Return 1st, 2nd, 3rd largest test values

[John]

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!

 

[Teethless mama]

rng1 is a defined name range

B2: =INDEX(rng1,MODE(MATCH(rng1,rng1,0)+{0,0}))
B3: =INDEX(rng1,MODE(IF(COUNTIF($B$2:B2,rng1)=0,MATCH(rng1,rng1,0)+{0,0})))

Both formale are required ctrl+shift+enter, not just enter
copy from B3 down

 

[T. Valko]

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,MATCH(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)

 

[John]

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]

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.

 

[alstubna]

Neat formulae. I can figure out what they're doing up to +{0,0}. Can you
explain what this is doing?

TIA

 

[Ron Coderre]

Per Excel Help:
"If the data set contains no duplicate data points,
MODE returns the #N/A error value"

So...the +{0,0} causes all values to be duplicated.
Consequently, any item that appears only once in the list would
have 2 values for MODE to evaluate:

Example:
If the results of the match function were: {1;1;1;4;4;4;4;8}
Then {1;1;1;4;4;4;4;8}+{0,0} results in: {1,1;1,1;1,1;4,4;4,4;4,4;4,4;8,8}
Notice how the single 8 becomes two 8's.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[John]

Yep, now we're rolln'!

Thanks!

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!