Regarding pulling out the top five most common numbers in a block of numbers

[AlwaysMe]

Hi All,

There's a closed thread in the forum titled "Pull out the top five most common numbers in a block of numbers", this is exactly what I need but the formula is for an early Excel
version and it only pulls out the first number on my 2013 version and I may need to use it on 2016 soon also. Does anyone know what I need to change?

[Glenn Kennedy]

Thi sarray formula will work in all EXCEL versions. In the attached file I used this formula:

=MODE(IF(ISNA(MATCH($A$1:$K$20,$N$1:$N1,0)),$A$1:$K$20))

to looka at the range of cells A1 to K20, which is full of numbers. Adjust as required to suit you.

I used this formula in N2 and entered it as an array formula.

If you want the list ELSEWHERE is CANNOT be in Row 1, as the row IMMEDIATELY above the list MUST be referred o in the formula, in the part: $N$1:$N1

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[AlwaysMe]

Thanks Glenn, it looks perfect! I'll play around with it some more and get back to you.