Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
I have a column with the values red, amber and green.
I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
Excel doesn't have a built in way of counting cells by color. You can
however use macros to do this: http://www.cpearson.com/excel/colors.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "MMcQ" wrote: I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
I actually have the words in the column as well as them colour coding based
on conditional formatting, can I do it using the text? "Dave F" wrote: Excel doesn't have a built in way of counting cells by color. You can however use macros to do this: http://www.cpearson.com/excel/colors.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "MMcQ" wrote: I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
=COUNTIF(A1:A100,"amber")
=COUNTIF(A1:A100,"red") =COUNTIF(A1:A100,"green") "MMcQ" wrote: I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
can it give me the most frequent value rather than me having to do three
separate counts, I actually want it to return the name of the text that appears most..... "Teethless mama" wrote: =COUNTIF(A1:A100,"amber") =COUNTIF(A1:A100,"red") =COUNTIF(A1:A100,"green") "MMcQ" wrote: I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
I don't quite understand your original post - if you just return the
largest number, how will you know which colour it relates to? You could put "amber", "green" and "red" in cells C1, C2 and C3, then in D1: =COUNTIF(A$1:A$100,C1) and copy this to D2 and D3 to show you all the values (adjust range as necessary). If you really want to you could try: =MAX(COUNTIF(A$1:A$100,"amber"),COUNTIF(A$1:A$100, "red"),COUNTIF(A$1:A $100,"green")) but this will only give you the maximum number. Hope this helps. Pete On Feb 9, 4:14 pm, MMcQ wrote: I actually have the words in the column as well as them colour coding based on conditional formatting, can I do it using the text? "Dave F" wrote: Excel doesn't have a built in way of counting cells by color. You can however use macros to do this:http://www.cpearson.com/excel/colors.htm Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "MMcQ" wrote: I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
Hello,
You can use advanced filter or: http://www.sulprobil.com/html/listfreq.html for example. Regards, Bernd |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MODE(MATCH(rng,rng,0))) Note: there must be a most frequent value for this to work. In other words, if all entries are unique you'll get an error. Also, if there are multiple instances of a mode (multimodal) the first instance will be returned. amber red green blue That sample will return #N/A since there is no mode. amber red amber red That sample will return amber because it is the first mode. Biff "MMcQ" wrote in message ... I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
count text values and return most common occurence
I had the same base question as MMcQ, and used your formula below to solve
the first part of the problem. I want the most common occuring text in A1, and the number of times that text occurs in the range in A2. I have the required formula for A2 =COUNTIF(C1:C16,A1), but I don't want the formula in A1 to count or return blanks. for example, for the range below, I want the cell to return the value "RED", not " " C1. AMBER C2. RED C3. C4. C5. C6. RED The third part of my problem is that I want another cell to return the SECOND most common text value, so the above range would result in the word "RED" in A1 and "AMBER" in A2 with their corresponding values in B1 and B2 Thanks in advance for your assistance "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(rng,MODE(MATCH(rng,rng,0))) Note: there must be a most frequent value for this to work. In other words, if all entries are unique you'll get an error. Also, if there are multiple instances of a mode (multimodal) the first instance will be returned. amber red green blue That sample will return #N/A since there is no mode. amber red amber red That sample will return amber because it is the first mode. Biff "MMcQ" wrote in message ... I have a column with the values red, amber and green. I want to count the values and in the calculation field, show the value which occurs most frequently. e.g. amber amber red green amber the calcualtion field should return amber |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |