Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Functions
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Functions
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SJT" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Database Functions
You can create a pivot table from the data, and use it to show a count
of each value. There are examples and links he http://www.contextures.com/xlPivot01.html SJT wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel database statistical functions (DSUM etc..) | Excel Discussion (Misc queries) | |||
database functions in excel | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Database Functions - question using formulas as criteria | Excel Worksheet Functions | |||
Pick certain rows from database to use statistical functions on th | Excel Worksheet Functions |