CODEWITHSUNDEEP
excelmode
kitchen800
kitchen800

Reputation: 227

Getting most common values using mode in excel

I have a list of 65 random numbers starting in column D2. I am trying to make a new list of number in column J2 that arranges these numbers by most frequent number.

I did this by tying the below formula into j2:

=MODE(IF(1-ISNUMBER(MATCH($D$2:$D$66,$J$1:J1,0)),$D$2:$D$66))

The problem is some numbers from column D are not appearing in column J. why is this?

Upvotes: 0

Views: 111

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

With values in D2 through D66, in E2 enter:

=COUNTIF($D$2:$D$66,D2)

and copy downwards:

enter image description here

Then sort both columns by column E descending:

enter image description here

This will capture all 65 items. If desired, you can perform a secondary sort on column D to cluster similar values.

Upvotes: 1

Related Questions