finding kth largest value in an array

[FluoroGuy]

I have a 100x100 cell array of numbers imported from another program. I want to pull out the top 10% of them, so I'm trying to find the number which is the cutoff for the top 10% - in other words I want to find the 1000th largest value of the 10,000 total
Kth largest in Descriptive Statistics in Data Analysis will only give you the value for a given column; similarly as far as I can tell functions like rank also only work on columns. How do I do this for an array of values
I'm a Visual Basic neophyte so if someone could show me how to do this without programming I'd be very indebted...

 

[Vasant Nanavati]

Use:

=LARGE(A1V,100)

I have a 100x100 cell array of numbers imported from another program. I

want to pull out the top 10% of them, so I'm trying to find the number which
is the cutoff for the top 10% - in other words I want to find the 1000th
largest value of the 10,000 total.

Kth largest in Descriptive Statistics in Data Analysis will only give you

the value for a given column; similarly as far as I can tell functions like
rank also only work on columns. How do I do this for an array of values?

I'm a Visual Basic neophyte so if someone could show me how to do this

without programming I'd be very indebted...

 

[Dave R.]

have you tried function LARGE?

I have a 100x100 cell array of numbers imported from another program. I

want to pull out the top 10% of them, so I'm trying to find the number which
is the cutoff for the top 10% - in other words I want to find the 1000th
largest value of the 10,000 total.

Kth largest in Descriptive Statistics in Data Analysis will only give you

the value for a given column; similarly as far as I can tell functions like
rank also only work on columns. How do I do this for an array of values?

I'm a Visual Basic neophyte so if someone could show me how to do this

without programming I'd be very indebted...

 

[Bob Phillips]

You can get it with a worksheet function,

=LARGE(A100V100,1000)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a 100x100 cell array of numbers imported from another program. I

want to pull out the top 10% of them, so I'm trying to find the number which
is the cutoff for the top 10% - in other words I want to find the 1000th
largest value of the 10,000 total.

Kth largest in Descriptive Statistics in Data Analysis will only give you

the value for a given column; similarly as far as I can tell functions like
rank also only work on columns. How do I do this for an array of values?

I'm a Visual Basic neophyte so if someone could show me how to do this

without programming I'd be very indebted...

 

[Ken Wright]

What do you need to do with the top 1000?

If you need to list them, then name your array of number MyArray, select cells
A1:A1000 and put the following formula into the formula bar and then array enter
it using CTRL+SHIFT+ENTER:-

=LARGE(MyArray,ROW(INDIRECT("1:1000")))

If you just need to Sum them, then try the following array entered:-

=SUM(LARGE(MyArray,ROW(INDIRECT("1:1000"))))

or

=SUMPRODUCT(LARGE(MyArray,ROW(INDIRECT("1:1000")))) without having to array
enter.

If you want to average them, then try the following array entered:-

=AVERAGE(LARGE(MyArray,ROW(INDIRECT("1:1000"))))


Substituting SMALL for LARGE will give you the opposites.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

I have a 100x100 cell array of numbers imported from another program. I want

to pull out the top 10% of them, so I'm trying to find the number which is the
cutoff for the top 10% - in other words I want to find the 1000th largest value
of the 10,000 total.

Kth largest in Descriptive Statistics in Data Analysis will only give you the

value for a given column; similarly as far as I can tell functions like rank
also only work on columns. How do I do this for an array of values?

I'm a Visual Basic neophyte so if someone could show me how to do this without

programming I'd be very indebted...

 

[Alan Beban]

That's the second person in this thread who seems to think that Column
DV is the 100th column; it is the 126th. Also, since A100V100 was
used (instead of A1V100), there can't be a 1000th largest out of 126
cells. Why is everyone in such a hurry to post without testing?

Alan Beban

 

[Dave R.]

Well...."everyone" aint

But for something so simple, with a function designed to do exactly this
with EASE, I thought the easiest way to direct this guy was to point out the
existence of the function. It's not as if he couldn't figure out how to use
LARGE all by himself, had he known about it. I got the impression from
reading his post that he knows something about excel, and something about
numbers, so (I think!) he isn't likely the type to just copy and paste
someone elses function in without figuring it out himself --especially when
it looks as easy as LARGE(x1:y100,1000)-- so it wasn't worth the extra
typing to lay the whole thing out for him-- and thus I avoided making a
mistake! Yay!

 

[Bob Phillips]

Alan,

The OP could have worked out both of those typos as he would know where the
data was. The important thing was to get across the LARGE function.

Do you really expect us to fill a 100x100 array just to test using such a
common function as LARGE?

Bob

 

[Harlan Grove]

...
...

Do you really expect us to fill a 100x100 array just to test using such a
common function as LARGE?

...

The curmudgeons are out tonight!

If you don't want to test, don't use dummy ranges, use arbitrary tokens such as

=LARGE(Array,1000)

Intent clear, and nothing for anyone else to gripe about (well, except for Alan,
who'd probably gripe about Array being left undefined).

 

[FluoroGuy]

Thanks to all - I knew there was something simple I was missing.

 

[Bob Phillips]

I will try to bear that in mind in future Mr Grove.

I certainly do not want to test to that extent, life's too short.

Bob

 

[Tushar Mehta]

Putting aside the question of whether one should test suggested
solutions or not, how long does it take to fill a 100x100 array?

Tools | Options... | General tab | select R1C1 display
CTRL+HOME
CTRL+G | specify r100c100 SHIFT+ENTER
Type =RAND() OPTION+ENTER

20 seconds? A little less? A little more?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Peo Sjoblom]

Or less than 10 seconds

Click in the name box, typ 1:100

press enter

=rand()

ctrl enter

 

[Ken Wright]

I do this almost every day chucking random data in to create misc arrays, so
it's a macro and a key combo for me:-

Sub RandomNumber()

ubnd = InputBox("Enter Upper Bound")
lbnd = InputBox("Enter Lower Bound")
nudp = InputBox("Just hit OK for Integers or type D for decimals")
If UCase(nudp) = "D" Then
With Selection
.ClearContents
.NumberFormat = "#,##0.00"
End With
For Each cell In Selection
cell.Value = Rnd() * (ubnd - lbnd) + lbnd
Next cell
Else
With Selection
.ClearContents
.NumberFormat = "#,##0"
End With
For Each cell In Selection
cell.Value = Int(Rnd() * (ubnd - lbnd + 1) + lbnd)
Next cell
End If

End Sub

 

[Alan Beban]

Putting aside the question of whether one should test suggested
solutions or not . . . .

A not unreasonable approach to apply to the vast majority of suggested
solutions is to either test or indicate that the suggested solution is
untested. The rest are of the simplest kind in which an error is highly
unlikely, a la Harlan Grove's =LARGE(Array, 1000).

Alan Beban