From XL Help:

*MODE:*

Returns the most frequently occurring, or repetitive, value in an array or range of data.

So let's say A2:A11 contains the following:

Thus: =MODE(A2:A11) returns 2

In your case however things are not quite so trivial, certainly not beyond the most common value... this is because you must exclude any numbers already returned when conducting your test.

If we say we want to return the top 3 values from A2:A11 in terms of their frequency in cells B2:B4 we use:

B2: =MODE(IF(ISNA(MATCH($A$2:$A$11,$B$1:$B1,0)),$A$2:$A$11))

committed with CTRL + SHIFT + ENTER

What this is doing is populating an array with ten values (one for each cell in A2:A11), if the number in A2:A11 can not be found in B1 (ie ISNA = TRUE) then the number from A2:A11 is returned to the Array, else a Boolean of FALSE is returned...

In the case of B2 (most common) no value in A2:A11 can be found in B1 so you end up with an array of:

{1;2;3;2;2;2;1;4;4;1}

This is then used in the MODE and the most common value is 2.

If we now shift to the 2nd most common value in A2:A11 our formula once copied from B2 will read as:

B3: =MODE(IF(ISNA(MATCH($A$2:$A$11,$B$1:$B2,0)),$A$2:$A$11))

(again committed with CSE)

Now in this case some of the values in A2:A11 will be found in the preceding range of B1:B2 given B2 contains 2... so you end up with an Array of:

{1;FALSE;3;FALSE;FALSE;FALSE;1;4;4;1}

You will note the FALSE replace the instances of 2...

The MODE will look only at the remaining numerical values (ie Booleans are ignored) so the most common value in that array is 1

To finish the example - the third most common value is generated in the next cell:

B4: =MODE(IF(ISNA(MATCH($A$2:$A$11,$B$1:$B3,0)),$A$2:$A$11))

(again committed with CSE)

This generates the following Array:

{FALSE;FALSE;3;FALSE;FALSE;FALSE;FALSE;4;4;FALSE}

Both 2 & 1 are now replaced by FALSE as these appear in the prior rows (B1:B3) and should thus be discounted from this calculation... the result of which will be 4.

Be sure to make use of XL Help and the Evaluate Formula option which can be very helpful when it comes to discerning the nature of what a formula is doing on execution.

I hope that helps.

EDIT: I should add that for B2 you could simply use MODE(A2:A11) however for consistency I applied the same formula to each cell in the results range, in terms of the above B2:B4 - in your file J4:J8.

## Bookmarks