The most occurence of a value in a column

I have 1 to 5 as values in a column. How can I find the number with the 
most occurrence in that column? 

Thanks,

cpliu
0
11/9/2004 6:47:24 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
617 Views

Similar Articles

[PageSpeed] 26

Hi
=MODE(A1:A100)

--
Regards
Frank Kabel
Frankfurt, Germany

"cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag
news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4...
> I have 1 to 5 as values in a column. How can I find the number with
the
> most occurrence in that column?
>
> Thanks,
>
> cpliu

0
frank.kabel (11126)
11/9/2004 6:52:45 PM
Try:

=INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF
(A1:A5,A1:A5),0))

Array-entered, meaning press ctrl/shift/enter.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have 1 to 5 as values in a column. How can I find the 
number with the 
>most occurrence in that column? 
>
>Thanks,
>
>cpliu
>.
>
0
Jason
11/9/2004 6:59:21 PM
Thank you all for the quick help. It works now.

Best regards,

cpliu
0
11/9/2004 7:10:04 PM
Reply:

Similar Artilces:

Automatic column width
Is it possible? I have a spreadsheet with one column in it in which the values may range between several thousand and several trillion. (These are money values so scientific notation is not an option.) The values in that column are all calculated on the basis of values in other columns' cells. It's annoying to have to adjust the column width every time the number of decimal places in the result change. -- Bob Kanyak's Doghouse http://kanyak.com Bob Bear in mind Excel will only 'resolute' to 15 numbers and then will add zeros unless you make it text and then you will no...

GP 10 Setup Values
Just started using GP10. When transferring an order to invoicing, is there a setup default that automatically checks the "Include totals and deposits" box on the transfer order to invoice screen?? Thanks, Dick, There is no way to do this out-of-the-box. -- Victoria Yudin Dynamics GP MVP Want to use Crystal Reports with GP? http://www.flex-solutions.com/gpreports.html blog: http://www.victoriayudin.com "Dick at VPI" <DickatVPI@discussions.microsoft.com> wrote in message news:90474D10-C2D8-4D80-8307-20644D0B93DD@microsoft.com... > Just...

sorting customer name and IP with value
any script to sort following list? i have the following IP address, customer name and value A B C IP address customer name value 10.10.10.1 Lee 10 10.10.10.2 wong 30 10.10.10.3 chan 40 10.10.10.4 chan 15 10.10.10.9 wong 16 10.10.10.11 Lee ...

Sort Contiguous Columns
I am working on an Excel 2000 VBA project. The users of the main sheet in this workbook complain that sometimes users will inadvertently sort only the first few columns of the sheet so that the data in the sheet gets "discombobulated" - the data in the sorted columns no longer relates properly to the data in the other, non-sorted columns. For example, if the sheet contains two columns - firstName and lastName, and the user sorts only the firstName column in alpha order, the names are no longer properly associated. Excel does not always warn the user that he is sorti...

Limiting an AVERAGE to a maximum number of values?
I've got a table for which I want to find the average of the right-most (filled) 12 columns. To be specific, the data in the table is organized by month, and I want to, for each given row, see what the average is for the prior year (12 months). For example, I have it set up that: Column J represents January 2003 data Column K represents February 2003 data Column L represents March 2003 data And so on... For each row, how do I average just the last 12 months, bearing in mind that if less than 12 months are used, it will average JUST those fields. For example, if (starting with Colu...

Cell Formatting Colours by Value
Sat my MOUS Expert today and something came up that I had never seen o heard of before. A cell had to be formatted to be red if below a value and includ brackets, whilst above the value it was coloured blue and withou brackets. This didn't use Conditional formatting, but was a custom format. I solved it by manipulating an already present custom format, bu really need to know how to do it now. Thank -- Message posted from http://www.ExcelForum.com Hi within custom format you can define up to three conditions to differentiate custom formats (and include a font color). See the Excel he...

How to change the value of a cell when a condition is satisfied, but not otherwise?
I have a small problem. My requirement is as follows: I have two cells and have enabled manual calculation. The first cell loops through the values 1-5 whenever F9 key is pressed. The function for this is -*B9*=IF(B9="1","2",IF(B9="2", "3", IF(B9="3", "4", IF(B9="4", "5", "1"))))- Whenever the value of cell B9 changes from 5 to 1, I need the value of cell C3 to change, but never otherwise. The complication is that cell C3 changes randomly every time B9 changes from value 5 to value 1. Right now, my form...

Hiding a zero Value plus 0.2
I am using this formula, =CELL("contents",C18)*24+0.2, and in the blank cell it puts 0.2 Is there anyway that the cell can be left blank until I enter data into the referenced (c18) cell? Thanks in advance for your help. Miles Reed Hi Miles, =IF(C18="","",CELL("contents",C18)*24+0.2) -- Kind Regards, Niek Otten Microsoft MVP - Excel <milesreed2@aol.com> wrote in message news:2f2601c4c119$f84e9820$a601280a@phx.gbl... >I am using this formula, =CELL("contents",C18)*24+0.2, > and in the blank cell it puts 0.2 Is there ...

Set a value every 5 cells
There is 1 data set is assumed running in A1 down, viz.: In A1 down is: 1, 2, 3, 4, 5, 6, 7, 8, 9 and 10 In the cell from B1 to B10 is empty (no value). My question is that I want a excel formule would performance a task which it can put a value e.g.1 every "5" cells. So, in my example, after setting the formula, "1" will incorporate in the cell B5 and B10. Do you think an Excel function can achieve the result according my requirement? Many thanks, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200902/1 You should alwa...

Getting value on other axis
I have XY scatter chart with data points connected by smooth lines. Values on X axsis are in the range from 0 to 1. Is there any way in Excel to find a value on Y axis corresponding to any value form 0 to 1 on X axsis based on plotted curve? Thank you, Gunnar You will have to build your own solution. If you visit http://xlrotor.com/, you'll find a link to the bezier curve method that mimics XL's smooth curves. If you want to use step wise linear interpolation, check out the Interactive Chart add-in available from my web site. -- Regards, Tushar Mehta www.tushar-mehta.com...

Formula to total items from a drop list with values in several col
I am trying to create a formula for the following: Have spreadsheet locate all columns matching "name" and total those columns total and provide grand total, please see example listed below Column A Column D Column G Column H Dog 10 35 Cat 15 10 Mouse 19 48 Cat 10 2 7 Cat 15 1 19 (All other colomns have various other information listed) Grand Totals: Dog = ? (answer: 45) Cat = ? (answer: 79) Mouse = ? (answer: ...

Combine clustered column with stacked column?
Is it possible to combine a stacked and clustered column chart? I have 12 series with 9 categories. For each of the 9 categories, I want 4 clustered columns, each one of the four a stacked column of 3 of the series. I haven't figured out how to do this yet. Is this possible and if so, how? Thanks, Eric Eric - You can make a clustered stacked column chart through clever arrangement of your data in the worksheet. See the details at Stephen Bullen's site: http://www.bmsltd.co.uk/Excel/SBXLPage.asp#Charting and look at the description for FunChart4 or at Bernard Liengme's si...

changing columns into rows
I'm a novice using Excel 2007 in W 7. I have an existing database where I would like to change things so that the rows become columns and vice versa. Is there a way to automatically do that or do I need to recreate the spreadsheet from scratch? Thanks, Jeff Depends upon the layout but have a look at Transpose in Help. You may be able to simply copy and paste special>transpose. Gord Dibben MS Excel MVP On Fri, 30 Apr 2010 13:12:41 -0400, "Jeff@nospam.fake" <Jeff@nospam.fake> wrote: >I'm a novice using Excel 2007 in W 7. >I have an exi...

Columns #2
Howdy -- I'm running Publisher 2002 and Windows XP. I formatted 500 words of text into a 3 column format, fully justified (Times Roman, 10 pt). All is okay except the last line of each paragraph. Whether it is one word or six words, that last line expands to full column width. When it's one word thus-- p l a n . -- it is unacceptable. What am I missing in the formatting? Any cures? Thanks -- Mike Michael LeFan The Write Place < www.lefan.com > 'Patience, My Foot!' <www.lefan.com/patience/> Voice: 254.773-3590 Fax: 254.231...

Formula to make Negative Values Positive & Positive Values Negative?
I would like to know if there is any way to have a formula/function that takes a value from another cell (I know how to do that part) and then makes the number negative if the original is positive - or else makes the number positive if the original is negative. Any ideas? -- mustard ------------------------------------------------------------------------ mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017 View this thread: http://www.excelforum.com/showthread.php?threadid=470855 Just multiply it by -1 example: =-1*A1 "mustard" wrote: ...

Retiring Asset with Non-Zero Net Book Value
Hi, I have recently started handling Fixed Assets and I have some doubts about the retiring assets in GP, When an asset is retired and has not been fully depreciated, the net book value is calculated as cost basis less LTD depreciation. Can anybody explain the underlying accounting concept for keeping the net book value. When an asset is retired, it means this asset has no value for the company. Isn=92t it so ? Is there any way in GP to make net value zero when it is retired ? Thanks. ...

How do I display a formula - not the value
Hi gurus, maybe this is a completely stupid question and I am blind to the obvious, but how can I display the formula in one cell as text in another cell? For example: A1 = 1 A2 = 2 A3 = "=A1+A2" A4 should show "A1+A2" (I know how to get rid of the "="...) And I know how to do this in VBA, but I would love to be able to avoid macros... Best regards and thanks in advance Youppie Hi AFAIK not possible with macros (you could use XLM macros as defined names but they could crash excel in versions prior to Excel 2003). So without vBA IMHO no solution "Youppi...

How do I find the highest of the most repeating values in a list of numbers ie. the max of the modes
Hi, I have a list of numbers and I need to find the highest of the most frequent occurrances. For example: List 1 1 3 3 4 4 Should return the value 4 because its the highest of the most frequent occurrances of any number. List 2 1 3 3 3 4 4 Should return 3 because it's the most frequent occurrance outright. I've tried using the mode command but it returns the first of the most frequent occurances but I want the highest of the most frequent occurrances. Any help would be greatly appreciated. Thanks Ana =max(mode(A1:A10)) Replace the range with required range. On Wed, 5 Jan ...

Largest value
I would like to have my query find the largest number in a field. I'm sure it is very simple, but I'm having a "Brain Fart" and can't remember how to do it. Any help will be appreciated....... -- Thanx, Hanksor Oregon City, Or SELECT MAX(fieldName) FROM tableName should do. "Hanksor" <nospam@stonergroup.com> wrote in message news:e15g7s7bHHA.4716@TK2MSFTNGP02.phx.gbl... >I would like to have my query find the largest number in a field. I'm sure >it is very simple, but I'm having a "Brain Fart" and can't rememb...

double a value & ROUND to next highest thousand
How do I double a number and round up to next highest thousand ie 35,200 X 2 = 70,400 Round to 71,000 Thanks for any help -- pgruening ------------------------------------------------------------------------ pgruening's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26011 View this thread: http://www.excelforum.com/showthread.php?threadid=393953 Try following function: =ROUNDUP(B3*2;-3) where B3 is a reference to a cell with value, which you want t calculate, -3 means "round to thousands" mlho -- mlho --------------------------------------...

Bound Column in Combo Box
Is it possible to have more than one bound column in a combo box? I need to search 2 txt fields of a query and would like to start to enter the text with autofill to select an item in either column for the parameter for a report. If it is possible, how would the [FORMS] be attached to the 2 separate fields in the query. carl Combo boxes can only have one bound column. You may need to use two combos. Steve "Carl" wrote: > Is it possible to have more than one bound column in a combo > box? I need to search 2 txt fields of a query and would like to start > to enter the ...

vlookup with 2 values
I want to use vlookup function , and lookup 2 values at a time. Is it possible with Vlookup or any other reference/lookup functions. Pls suggest. Thanks in advance -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17590 View this thread: http://www.excelforum.com/showthread.php?threadid=483041 Hi! Providing some (any!) detail would help! A basic formula for multiple lookup values: =INDEX(C1:C10,MATCH(1,(A1:A10=Lookup_1)*(B1:B10=Lookup_2),0)) Array entered using ...

VLOOKUP double values
HI there, Does anyone know whether it is possible to carry out a VLookup formulae by taking two cells data from the same row and look up a value in a differnent worksheet which has the same values in the same row? I hope i have explained myself correctly, it's not an easyone to explain. Thanks for you time Kind Regards, SWM -- swmasson ------------------------------------------------------------------------ swmasson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8385 View this thread: http://www.excelforum.com/showthread.php?threadid=373870 Hi Yo...

Duplicates in column
Hi all I have a userform which I have linked a combobox upto column "A" in my worksheet. What I have done is place some code that copies the information in this box when exited into two postions in the workbook. The problem I am having is when something new is added it is ok and it just adds to the list, but when an older item is used it places it into the list aswell so I then start having duplicates. Is there a code I can use on the combobox to stop this from occuring? If not how can I change this code to search the whole column and remove the duplicates. This is the code ...

Summing values in a column if the date in another is of a certain month
Hi, I hope that this should be quite easy, but I'll be blowed if I can find an answer on google... Column F has dates, some rows may be blank Column G has currency values, some rows may be blank but if a row in column F has a value, so will the corresponding row in column G And what I would like to do is produce on another sheet, the total per month of each year (as set by column F) of the values in column G. i.e. December 2006 1234 January 2007 5678 Any help appreciated. -- Best regards Harvey Coward =SUMPRODUCT(--(YEAR(Sheet1!$F$2:$F$200)=YEAR(A1)),--(MONTH(...