Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I show 1st, 2nd, and 3rd most repeated non-numeric values?
I have several categories that are repeated numerous times. For example,
most eaten fruit in the cafeteria... apples, oranges, bananas... I want to put in a formula that will count how many times each fruit is repeated in the data, and if apple is top repeat, show apple in the cell... Then if banana is second I want to show banana in the next cell. etc.??? Any help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I show 1st, 2nd, and 3rd most repeated non-numeric values?
Here's the easy way to do this:
Use the Advanced Filter to extract the unique values. Choose to copy those unique values to another location. Suppose those unique values are copied to the range A1:A5. In B1 enter a formula like this: =COUNTIF(J$1:J$20,A1) Where J1:J20 is the location of the list with dupes. Drag copy the formula in B1 down to B5. Then sort A1:B5 on column B descending. Biff "dboy" wrote in message ... I have several categories that are repeated numerous times. For example, most eaten fruit in the cafeteria... apples, oranges, bananas... I want to put in a formula that will count how many times each fruit is repeated in the data, and if apple is top repeat, show apple in the cell... Then if banana is second I want to show banana in the next cell. etc.??? Any help? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I show 1st, 2nd, and 3rd most repeated non-numeric values?
Here is another way that takes a bit more setup, but works with the data
dynamically. It is not as simple, but it is interesting to see how it works. Contain the data to be analyzed in a named-range "data". (Highlight the cells containing the data and type "data" into the name box and hit "enter".) ////////////////// Create a list of the unique items within the data to be counted and ranked. Select the entire list and make it a named-range called "items". /////////////////// The four columns to the right of the "items" list will be used for the calculations. Select the cells next to the "items" list and name them "count". Select the top cell in the "count" named-range and name it "countTop". Copy the following formula into all the cells of the "count" range: =IF(ROW()-ROW(CountTop)<COUNTA(Items),COUNTIF(Data,Items),"" ) The "count" range will now contain a count value for each item in the data list. ///////////////////// Next select the cells to the right of the "count" range. Name this range "RankUnsorted". Insert the following formula into all the cells of the "RankUnsorted" range: =IF(ROW()-ROW(CountTop)<COUNTA(Items),RANK(Count,Count)+COUN TIF(CountTop:INDIRECT(ADDRESS(ROW(),COLUMN()-1)),Count)-1,"") This will rank the count values. //////////////////// In the next column, copy the following formula in all the cells next to the ranking values: =IF(ROW()-ROW(CountTop)<COUNTA(Items),INDEX(Items,MATCH(ROW( )-ROW(CountTop)+1,RankUnsorted,0)),"") This will return the list of items sorted from highest frequency to lowest frequency. /////////////////// In the next column, copy the following formula in all the cells next to the sorted items: =IF(ROW()-ROW(CountTop)<COUNTA(Items),INDEX(Count,MATCH(ROW( )-ROW(CountTop)+1,RankUnsorted,0)),"") This will return the frequency for each item in the data list. Note that ties are listed in the order that each item was encountered in the "items" list. /////////////////// So, in the end you will have a named-range containing all the data and seperately a table of five columns. The first 3 columns of the table are named ranges, the top cell of the 2nd column is a single-cell named range ("countTop"), and the last 2 columns display the output. The original idea here is not mine, I found something simular and then modified it. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i show non-numeric values graphically in a chart? | Charts and Charting in Excel | |||
can i show repeated values in a pivot table? | Excel Discussion (Misc queries) | |||
Return Numeric Labels that have different Numeric Values | Excel Worksheet Functions | |||
Repeated values in a row | Excel Discussion (Misc queries) | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |