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.
=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.
Great Response this worked for me except for the second one it gives me a value of 0 but this works for me Thank you Legend