Forum Discussion

Copper Contributor

Sep 29, 2020

Solved

Finding the most frequently occurring values in a data set

Hello,

I have a data set of Zip codes, and need to find which are the most frequently occurring zips in the set.

Could someone help me figure this out?

Thanks!

BI & Data Analysis

Formulas and Functions

HansVogelaar
Sep 29, 2020
Khamablet

Let's say the ZIP codes are in A2:A100.

If the ZIP codes are numbers such as 20500, you can use

=MODE(A2:A100)

to return the most frequently occurring ZIP code.

If they are text values, such as SW1A 1AA, you can use the following array formula, confirmed with Ctrl+Shift+Enter:

=INDEX(A2:A100,MODE(MATCH(A2:A100,A2:A100,0)))

2 Replies

NikolinoDE
Platinum Contributor
Sep 29, 2020
With the permission of everyone involved, if I may add to Mr. Hans Vogelaar
formulas.
In case that several occur equally often:
=MODE.MULT(zahl1,Zahl2,...)
Complete the matrix function with the key combination CTRL + Shift + ENTER

Thank you for your understanding and patience

Nikolino
I know I don't know anything (Socrates)
HansVogelaar
MVP
Sep 29, 2020
Khamablet

Let's say the ZIP codes are in A2:A100.

If the ZIP codes are numbers such as 20500, you can use

=MODE(A2:A100)

to return the most frequently occurring ZIP code.

If they are text values, such as SW1A 1AA, you can use the following array formula, confirmed with Ctrl+Shift+Enter:

=INDEX(A2:A100,MODE(MATCH(A2:A100,A2:A100,0)))