MrExcel Publishing
Your One Stop for Excel Tips & Solutions

top 5 ,bottom 5

Posted by Trevor Smith on March 10, 2000 12:03 PM

My problem is I would like to find the top 5 and bottom 5 values from a variable sized range. I was wondering if there was an easy way.......thanks

Posted by Jaime on March 10, 2000 1:35 PM

This is an array function it covers five cells, ur five top values.It assumes ur placing the values in the range(b2:"end of worksheet").

This array function is placed on the top row of the shhet in order to avoid a circular ref.

The smallest 5 values is obtainded with the same formula just change Large for Small.

Posted by Trevor on March 11, 2000 1:26 PM

Thanks to Jaime, I am not sure where you are but this was the first time I have used a forum of this type, and it blows me away that my problem was posted and an hour or so later someone somewhere in the world took the trouble to help solve my problem.....I warn you I'll be back for more............again thanks

Posted by Jaime on March 11, 2000 9:31 PM