five highest, five lowest 2

[eHanSolo]

hi all,

if i have a list of random numbers, both negative and positive. how can obtain the highest 5 and lowest five and paste it on a different sheet without having to manually sort it first?

using max or min would only get me two values.

maybe some vba is needed here?

any help would be good.

thanks.

e

 

[mrmovie]

only thing i can think of is a bubble sort on a array.
might be less code if you bubble sort on an array with 6 elements and then bin off the last element.

you can avoid bubble sorting for every random number by checking your number against the last element in the array, if it is higher then go through the 6 elemennt bubble sort thang.

 

[rmikesmith]

Have a look at the LARGE and SMALL worksheet functions, which will allow you to select the kth largest and kth smallest value in an array or range, respectively.


Regards,
Mike

 

[TonyJollans]

Look at the LARGE and SMALL functions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Almost got there

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[eHanSolo]

fantastic... works a treat!!!

*bear hugs all of them*

thank you again!


Best,

e

 

[rmikesmith]

Tony,

I think the difference was literally seconds

Mike

 

[IknowMe]

9.5 out of 10 on the synchronized typing, and taught me something to boot. Stars for both.