Forum Discussion
Solved
Looking for top 5 common numbers in a sheet
I have a sheet for example from a11 to k350 each cell has a number I want to find the 5 most common numbers on the sheet ? I can find the most common number by using =MODE(A11:K350) now I wan...
=MODE(A5:I26)=MODE(IF(A5:I26<>C1,A5:I26))=MODE(IF((A5:I26<>C1)*(A5:I26<>C2),A5:I26))Maybe with these formulas in cells C1, C2 and C3. Enter the formulas in cells C2 and C3 with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
The ranges can be adapted as required and the formulas for 4th, 5 th, 6th... most common value can be set up accordingly.
For O365:
=LET(
ζ, 5,
ξ, A11:K350,
κ, COUNTIF(ξ, ξ),
TAKE(UNIQUE(SORTBY(TOCOL(ξ), TOCOL(κ), -1)), ζ)
)
where ζ (5 here) represents the number of returns to be made.
ok I think this could get me somewhere but so far it returns
#SPILL!
#SPILL!
- You need to make sure there are a sufficient number of empty cells under the one in which you entered the formula. If you are setting ζ to 5, as I did, then you'll need a further four unoccupied cells under that which contains the formula.
- yes defiantly enough empty cell
Then perhaps you were using a function such as RANDBETWEEN or RANDARRAY to generate the values in your range, which will cause intermittent #SPILL! errors.