SOLVED

Finding the most frequently occurring values in a data set

%3CLINGO-SUB%20id%3D%22lingo-sub-1726039%22%20slang%3D%22en-US%22%3EFinding%20the%20most%20frequently%20occurring%20values%20in%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1726039%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20data%20set%20of%20Zip%20codes%2C%20and%20need%20to%20find%20which%20are%20the%20most%20frequently%20occurring%20zips%20in%20the%20set.%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20someone%20help%20me%20figure%20this%20out%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1726039%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1726069%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20the%20most%20frequently%20occurring%20values%20in%20a%20data%20set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1726069%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813817%22%20target%3D%22_blank%22%3E%40Khamablet%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20ZIP%20codes%20are%20in%20A2%3AA100.%3C%2FP%3E%0A%3CP%3EIf%20the%20ZIP%20codes%20are%20numbers%20such%20as%2020500%2C%20you%20can%20use%3C%2FP%3E%0A%3CP%3E%3DMODE(A2%3AA100)%3C%2FP%3E%0A%3CP%3Eto%20return%20the%20most%20frequently%20occurring%20ZIP%20code.%3C%2FP%3E%0A%3CP%3EIf%20they%20are%20text%20values%2C%20such%20as%20SW1A%201AA%2C%20you%20can%20use%20the%20following%20array%20formula%2C%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%3DINDEX(A2%3AA100%2CMODE(MATCH(A2%3AA100%2CA2%3AA100%2C0)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
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!
2 Replies
Highlighted
Best Response confirmed by allyreckerman (Microsoft)
Solution

@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)))

Highlighted
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)