Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have a column with the values red, amber and green, in text format, not
just a colour in the cell. 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
|
|||
|
|||
![]()
=INDEX(rng,MATCH(MAX(COUNTIF(rng,rng)),COUNTIF(rng ,rng),0))
it is an array formula, so commit with Ctrl-Shift-enter, not just Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MMcQ" wrote in message ... I have a column with the values red, amber and green, in text format, not just a colour in the cell. 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
|
|||
|
|||
![]()
Try this:
With A1:A20 containing the values (text, numeric, or blanks) This ARRAY FORMULA returns the most occurring non-blank value B1: =INDEX(A1:A20,MATCH(MAX(COUNTIF(A1:A20,A1:A20)),CO UNTIF(A1:A20,A1:A20),0)) Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP "MMcQ" wrote: I have a column with the values red, amber and green, in text format, not just a colour in the cell. 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
|
|||
|
|||
![]()
What if the data contains more than one value occurring the most? For
example, if the data contains the following... amber amber red red green blue yellow yellow brown ....what result do you expect? In article , MMcQ wrote: I have a column with the values red, amber and green, in text format, not just a colour in the cell. 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 | |
|
|
![]() |
||||
Thread | Forum | |||
Count the number of times a cell value is within a specific range | Excel Worksheet Functions | |||
how do i count text and display it as text plus the # times it hap | New Users to Excel | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
how to count the number of text frequencies and copy to other cell | Excel Worksheet Functions | |||
count the number of times the same number shown | Excel Discussion (Misc queries) |