Listing first,second,third etc most common valuse in a range of cells

Firstly:

IF(COUNTIF(C$2:C2,A$2:A$10)=0

The formula starts (I assume) in C3....So in the first cell its looking in C2 to see if the number appears in your range. It doesn't. The =0 condition is met, so it goes on to the next part of the formula with an array of {TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE}. When the formula is copied down into C4, your most frequently occurring number is in C3. the formula now looks in C2 to C3 to see which numbers in A2 to A10 do NOT appear in that range IF(COUNTIF)=0. So your answer in C3 is excluded and it will return an array of (e.g.) {TRUE FALSE FALSE TRUE FALSE TRUE TRUE TRUE TRUE}, or whatever.


Next bit. MATCH($A$2:$A$10,$A$2:$A$10,0) returns ALL the values in A2 to A10 as an array (9 values in all). The +[0,0} bit repeats each number. So, if in A2 downwards you have 4,1,1,3,2 etc... the +{0,0} bit returns 4,4,1,1,1,1,3,3,2,2,etc... This is needed because MODE will NOT work when n=1. By including that, the MINIMUM possible number of instances of a number is now 2. So MODE will happily find the least most frequently occurring number(s): i.e. any/all that have a frequency in your original data of 1.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.