CODEWITHSUNDEEP
google-sheets
Amanda
Amanda

Reputation: 35

How to calculate ALL modes in a Google Sheets?

I have a data set (11,11,14,14,10).

My goal is to return all frequently appeared numbers. I used =mode() function.

But, it does not return 11 and 14, only returns 11.

Any ideas/thoughts on that?

Upvotes: 3

Views: 242

Answers (2)

player0
player0

Reputation: 1

=QUERY(QUERY(A1:A, "select count(A), A 
                    group by A 
                    order by count(A) desc"),
                   "select Col2 where Col1 > 1", 0)

0

=TRANSPOSE(QUERY(QUERY(QUERY(TRANSPOSE(E1:1), "select *"),
                                              "select count(Col1), Col1 
                                               group by Col1 
                                               order by count(Col1) desc"),
                                              "select Col2 where Col1 > 1", 0))

4

Upvotes: 0

pnuts
pnuts

Reputation: 59485

With layout as shown,

=query(A2:A6,"select count(A), A group by A order by count(A) desc label count(A) 'frequency'")

should return a listing of all frequencies in descending order:

SO54279058 example

Upvotes: 1

Related Questions