Database Functions

S

SJT

I am looking for but cant' seem to find a database function that will
identify for me the entry that appears most often in a given column and
second most frequently in that same column. Is there such a function or
formula? Thank you in advance for your assistance.
 
R

Ragdyer

Most frequently occurring value:

=MODE(A1:A20)

Try this *array* formula for the second most frequently occurring value:

=MODE(IF(A1:A20<>MODE(A1:A20),A1:A20))

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

Caveat:
In the case of ties, first number displayed in list takes precedence.

With the array formula for the 2nd most used value, blank rows are counted
as 0's, and 0 will be returned if they ( 0's ) outnumber any value.
 
Top