Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding n-th most frequently occuring word
I am trying to find the n-th most frequently occurring name is a database. I
have a formula that finds the top most frequently occurring name =INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0)) this being entered as an array. How would I find the second, third, fourth, etc. most frequently occurring name? Example: George Ilysha Steven George Steven George Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding n-th most frequently occuring word
Sorry, i entered the incorrect array formula, my formula for the top most
frequent is ACTUALLY =INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ),0)) "Matt Stayton" wrote: I am trying to find the n-th most frequently occurring name is a database. I have a formula that finds the top most frequently occurring name =INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0)) this being entered as an array. How would I find the second, third, fourth, etc. most frequently occurring name? Example: George Ilysha Steven George Steven George Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding n-th most frequently occuring word
Try these array formulas** :
For the most frequent item: =INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))) Assume that formula is entered in cell C1. For the nth most frequent items in descending order, entered in cell C2 and copied down as needed: =INDEX(A$1:A$20,MODE(IF(COUNTIF(C$1:C1,A$1:A$20)=0 ,MATCH(A$1:A$20,A$1:A$20,0)+{0,0}))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Matt Stayton" <Matt wrote in message ... I am trying to find the n-th most frequently occurring name is a database. I have a formula that finds the top most frequently occurring name =INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0)) this being entered as an array. How would I find the second, third, fourth, etc. most frequently occurring name? Example: George Ilysha Steven George Steven George Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding n-th most frequently occuring word
In B1: =INDEX(rng,MODE(MATCH(rng,rng,0)+{0,0}))
In B2: =INDEX(rng,MODE(IF(COUNTIF($B$1:B1,rng)=0,MATCH(rn g,rng,0)+{0,0}))) Ctrl+Shift+Enter on both formula listed above, and copy from B2 down as far as needed. "Matt Stayton" wrote: Sorry, i entered the incorrect array formula, my formula for the top most frequent is ACTUALLY =INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ),0)) "Matt Stayton" wrote: I am trying to find the n-th most frequently occurring name is a database. I have a formula that finds the top most frequently occurring name =INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0)) this being entered as an array. How would I find the second, third, fourth, etc. most frequently occurring name? Example: George Ilysha Steven George Steven George Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
finding n-th most frequently occuring word
With these formulas my cells say #N/A.
what may i be entering incorrectly? "Teethless mama" wrote: In B1: =INDEX(rng,MODE(MATCH(rng,rng,0)+{0,0})) In B2: =INDEX(rng,MODE(IF(COUNTIF($B$1:B1,rng)=0,MATCH(rn g,rng,0)+{0,0}))) Ctrl+Shift+Enter on both formula listed above, and copy from B2 down as far as needed. "Matt Stayton" wrote: Sorry, i entered the incorrect array formula, my formula for the top most frequent is ACTUALLY =INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ),0)) "Matt Stayton" wrote: I am trying to find the n-th most frequently occurring name is a database. I have a formula that finds the top most frequently occurring name =INDEX(rng,MATCH(LARGE(UNIQUEVALUES(COUNTIF(rng,rn g)),2),COUNTIF(rng,rng),0)) this being entered as an array. How would I find the second, third, fourth, etc. most frequently occurring name? Example: George Ilysha Steven George Steven George Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a word in CSV files | Excel Discussion (Misc queries) | |||
Return Frequently occuring Rows, based on COUNT | Excel Worksheet Functions | |||
Extracting the most frequently occuring text from a range | Excel Worksheet Functions | |||
Finding All but Last Word | Excel Worksheet Functions | |||
Finding specific word in column | Excel Worksheet Functions |