Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Most common occurrence of a string
Hello.
I need a worksheet function that will tell me the most frequent occurrence of a single character string in a range of cells, the second most, and the third most. Example A B C D E F 1 2 A A B B A C 3 4 1st 2nd 3rd 5 A B C So a formula in A5 that gives the most common occurrence in range A2:F2. A formula in B5 that gives the second most frequent occurence and a forumula in C5 that gives the 3rd. -- Thanks, Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Most common occurrence of a string
Maybe something like this:
With A1:F1 containing various text values A6: 1 B6: 2 C6: 3 The most occurring item is A5: =INDEX($A$1:$F$1,1,MATCH(LARGE(FREQUENCY(MATCH($A$ 1:$F$1,$A$1:$F$1,0),MATCH($A$1:$F$1,$A$1:$F$1,0)), A6),FREQUENCY(MATCH($A$1:$F$1,$A$1:$F$1,0),MATCH($ A$1:$F$1,$A$1:$F$1,0)),0)) Copy that formula across thru C5 for the 2nd and 3rd most occurring items Note: That formula will have problems with ties and blanks. So if those may exist, let us know so we can account for them. Does that help? *********** Regards, Ron XL2002, WinXP "Mike Archer" wrote: Hello. I need a worksheet function that will tell me the most frequent occurrence of a single character string in a range of cells, the second most, and the third most. Example A B C D E F 1 2 A A B B A C 3 4 1st 2nd 3rd 5 A B C So a formula in A5 that gives the most common occurrence in range A2:F2. A formula in B5 that gives the second most frequent occurence and a forumula in C5 that gives the 3rd. -- Thanks, Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display common values in first occurrence only | Excel Discussion (Misc queries) | |||
Occurrence between rows | Excel Discussion (Misc queries) | |||
V Lookup 2nd Occurrence | New Users to Excel | |||
find last occurrence | Excel Worksheet Functions | |||
occurrence (last) reference | Excel Discussion (Misc queries) |