yogi_Find 2nd 3rd 4th 5th 6th 7th 8th 9th And 10th Mode Of Values In A Range Covering Cells A1:E50

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #572    Jun 02, 2012     www.energyefficientbuild.com.

user klee025 said:
How to find the 2nd, 3rd, 4th, 5th, 6th, 7th, 8th, 9th, 10th, most common numbers in Multiple Spreadsheets 
Hey Guys,

got a question for you that I am trying to figure out.

I have 5 spread sheets. Lets just call them Spreadsheet A, B, C, D, E

In each spreadsheet, there are 6 columns of numbers, which I will select as an entire range.

But for arguments sake, lets just say A1:A50, B1:B50, C1:C50, D1:D50, E1:E50

I know how to get the most common number by using MODE.

Im trying to figure out a formula with MODE that will give me the 2nd most common number referencing Spreadsheet A, B, C, D, E with A1:A50, B1:B50, C1:C50, D1:D50, E1:E50

Then using the same model, get the the 3rd most frequent number

4th most frequent number

5th, 6th, 7th, 8th, 9th, all the way to the 10th most frequent number.

I would be so grateful to anyone that can clearly give me a copy-paste function that I can just incorporate right away.

Thank You!!

-------------------------------------------------

As of this time (Jun-2012) the MODE function in Google spreadsheet does not provide correct result ... so I have used a workaround to determine the values for different modes