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

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

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

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

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

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 -

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

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

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