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 want to find the second most common
third, forth & 5th and possibly the top ten ?
HELP! Me Please
=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.
Many roads lead to Rome and even more roads to a destination in Excel :).
With pivot table
You have a column heading (e.g. "Values") in A1 and your values in A11:A350.
Following procedure:
1. Highlight A11:A350.
2. DATA - PIVOT TABLE REPORT - next - next
3. Drag the "Values" button into the "Row" and "Data" fields.
4. Double-click on "Total values" and then select "Number".
5. Next - endThis can be done with formula with additional auxiliary columns:
Example with 4 auxiliary columns.
As assumed, the data is in A11:K350.
Then in A1:
=MODE(A2:K350)
and copy to E1.
In B2
=IF(A2=A$1;"";A2)
and copy to E.The 5 most common values are then in A1:E1, in ascending order of frequency, i.e. the most common in A1, the second most common in B1, etc.
I hope that I could help you with this.
I know I don't know anything (Socrates)
=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
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!- 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.
