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

[Lasz1000]

Hi,

I hope someone could help me out with an interpretation of a code I found:

I have a column with random numbers (in cells A2 to A10) and I have to find out the order of their frequency (into column C) in descending order. I have found one solution to the problem and it helped.

(link: https://www.mrexcel.com/forum/excel-...ing-array.html
and the answer was provided by T.Valko)

I pasted the code into my excel file and was able to obtain the most frequent values in my range in a descending order which is great.



My question here is if someone could help me interpret the line of code that was used:

={IFERROR(INDEX(A$2:A$10,MODE(IF(COUNTIF(C$2:C2,A$2:A$10)=0,MATCH(A$2:A$10,A$2:A$10,0)+{0,0}))),"")}

I know how INDEX and MATCH works together, I also know how COUNTIF works and I am familiar with ARRAYS in general, but in the above function I do not understand the following:

1. COUNTIF: by syntax this function requires RANGE and CRITERIA, but in the above code there are two ranges given. How does it work then?
2. MATCH: same as with COUNTIF, by syntax it needs a LOOKUP VALUE and an ARRAY, but above the code has the same range given for both LOOKUP VALUE and ARRAY. How does it work whats ists purpose?
3. Lastly, within the MODE part there is an addition of {0,0}, without which the function does not work. I know its an array, but no idea of its significance here.

Thanks in advance for any help/ guiance on this

[Glenn Kennedy]

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.

[Lasz1000]

Thanks it makes more sense now

[Glenn Kennedy]

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.