most frequently occurring value

hello


how do i find out the most frequently occurring value, digit, or numbe
in a set of rows & columns ?
then i need to find out the second most occurring value then the 3rd

then find the least frequently occurring value
then the second least occurring value then the 3rd


thanx..

--
Pivotren
-----------------------------------------------------------------------
Pivotrend's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=406
View this thread: http://www.excelforum.com/showthread.php?threadid=49572

0
12/23/2005 7:15:08 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
579 Views

Similar Articles

[PageSpeed] 33

Look in HELP for the FREUQUENCY() function

-- 
Kind regards,

Niek Otten

"Pivotrend" <Pivotrend.20herf_1135322414.6477@excelforum-nospam.com> wrote 
in message news:Pivotrend.20herf_1135322414.6477@excelforum-nospam.com...
>
> hello
>
>
> how do i find out the most frequently occurring value, digit, or number
> in a set of rows & columns ?
> then i need to find out the second most occurring value then the 3rd
>
> then find the least frequently occurring value
> then the second least occurring value then the 3rd
>
>
> thanx...
>
>
> -- 
> Pivotrend
> ------------------------------------------------------------------------
> Pivotrend's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=4062
> View this thread: http://www.excelforum.com/showthread.php?threadid=495720
> 


0
nicolaus (2022)
12/23/2005 8:48:49 AM
<FREUQUENCY()>

make that

FREQUENCY()

-- 
Kind regards,

Niek Otten

"Niek Otten" <nicolaus@xs4all.nl> wrote in message 
news:O33Nv25BGHA.1312@TK2MSFTNGP09.phx.gbl...
> Look in HELP for the FREUQUENCY() function
>
> -- 
> Kind regards,
>
> Niek Otten
>
> "Pivotrend" <Pivotrend.20herf_1135322414.6477@excelforum-nospam.com> wrote 
> in message news:Pivotrend.20herf_1135322414.6477@excelforum-nospam.com...
>>
>> hello
>>
>>
>> how do i find out the most frequently occurring value, digit, or number
>> in a set of rows & columns ?
>> then i need to find out the second most occurring value then the 3rd
>>
>> then find the least frequently occurring value
>> then the second least occurring value then the 3rd
>>
>>
>> thanx...
>>
>>
>> -- 
>> Pivotrend
>> ------------------------------------------------------------------------
>> Pivotrend's Profile: 
>> http://www.excelforum.com/member.php?action=getinfo&userid=4062
>> View this thread: 
>> http://www.excelforum.com/showthread.php?threadid=495720
>>
>
> 


0
nicolaus (2022)
12/23/2005 9:45:32 AM
Assuming that the numbers are in A2:A200

B2: = A1
B3: enter

=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$200&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$200),"",$A$2:$A$200),MATCH(0,COUNTIF(B$2:B2,$A$2:$A
$200&""),0)))

as an array formula, so commit with Ctrl-Shift-Enter

Copy this as far down column B as you think you might have unique values in
column A

Select C2:Cn, where n is the number of uniques previously estimate (I have
estimated down to 10 for this exercise),  and then in the formula bar enter
this formula

=IF($B$2:$B$10<>"",FREQUENCY($A$2:$A$200,$B$2:$B$10),"")

again an array formula, committed with Ctrl-Shift-Enter
-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pivotrend" <Pivotrend.20herf_1135322414.6477@excelforum-nospam.com> wrote
in message news:Pivotrend.20herf_1135322414.6477@excelforum-nospam.com...
>
> hello
>
>
> how do i find out the most frequently occurring value, digit, or number
> in a set of rows & columns ?
> then i need to find out the second most occurring value then the 3rd
>
> then find the least frequently occurring value
> then the second least occurring value then the 3rd
>
>
> thanx...
>
>
> -- 
> Pivotrend
> ------------------------------------------------------------------------
> Pivotrend's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4062
> View this thread: http://www.excelforum.com/showthread.php?threadid=495720
>


0
bob.phillips1 (6510)
12/23/2005 11:57:58 AM
Reply:

Similar Artilces:

Where are frequent flyer accounts?
I recently moved to a new machine and took my Money file with me. In account list, frequent flyer accounts use to show up at the bottom. They aren't there anymore. Is there an option to get them back? Using MS Money Plus Deluxe. ...

Would like to show a Blank cell on a #value! error
I am entering a date in one cell, then in another cell I am using =sum(a1-7) to sub 7 days from the date entered in cell A1. The problem is that if a date has not yet been entered in A1, then the formula cell displays #value I would like to hide this error or just show a blank cell. Rick, =IF(A1<>"",A1-7,""). Also note that in your original formula the SUM was not necessary -- A1-7 is all you needed so far as the arithmetic was concerned. -- DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "Rick" <anonymous@discussions...

Frequent log entries, event 9175 and 8197
Hello! I have just deployed a fresh Exchange 2003 installation on an equally fresh Windows Server 2003. I have the following problems: 1. OWA and Outlook works, but it is impossible to add new meeting items via OWA. 2. The event log is filled with event 9175. This gets logged once a minute: ------------------------- The MAPI call 'OpenMsgStore' failed with the following error: The Microsoft Exchange Server computer is not available. Either there are network problems or the Microsoft Exchange Server computer is down for maintenance. The MAPI provider failed. Microsoft Exchange Serv...

counting non occur entries
hi all, i have two lists L1 in the range A2:A30 and L2 in the range K15:K240, like to know the number of occurrences in L1 which do not occur in L2. thanks for any help Hi COUNTA(A2:A30)-SUMPRODUCT(--(ISNUMBER(MATCH(A2:A30,K15:K240,0)))) -- Regards Frank Kabel Frankfurt, Germany "excelFan" <excelFan@discussions.microsoft.com> schrieb im Newsbeitrag news:2C8CC295-2A8E-4E9B-BDDB-106AF4090ABE@microsoft.com... > hi all, > i have two lists L1 in the range A2:A30 and L2 in the range K15:K240, like > to know the number of occurrences in L1 which do not occur in L2. &...

Vista Mail frequently blocks when deleting mail items
When deleting mail items from my various folders Mail frequently blocks. Closing and restarting mail sometimes works, but often I need to close the Winmail process (process manager) and then restart. Is there a solution to this? -- JeanDanc Make sure you have installed Vista's SP1 and SP2 updates. If the problem continues, try running the various repair functions in=20 the WMUtil program: http://www.oehelp.com/WMUtil If still no improvement, upgrade to Windows Live Mail: http://download.live.com/wlmail=20 =20 --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP pro...

laptop disconnects frequently
I have a customer SBS 2003 Standard setup with 10 users. One user on a laptop is constantly being disconnected from the network for about 30 seconds to a few minutes then reconnects. I have changed cables, tried wireless, tried different locations using different cables, switched ports on the switch and even formatted and reloaded the system and still he is getting disconnected. It was good for about a day after I switched ports on the 16-port switch but that was it. Sometimes he receives a message saying that this computer is trying to use the same IP as another device on th...

How to combine text and the value in another cell?
Excel 2007 Lets say in A2 I have a calculated value of 2 in A1 I want it to say The total of XX = 2 This is what I tried: =CONCATENATE(The Total of XX =,A2) Gave me a "value" error what other approach should I try? tia dave Try this: ="The total of xx = "&A2 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Dave" <dave@accessdatapros> wrote in message news:20C0AC3C-2CF3-49BC-8837-428F60A42692@microsof...

HELP: A severe error occurred on the current command.
We have encountered the following error message when hitting a particular trigger. This trigger hasnt been altered for nearly 2 years. "Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded." The SQL Server in question is version 9.00.4035.00 SP3 Standard Edition (64-bit). I have taken backup of the DB and restored it to my local machine which has SQL Server 9.00.4285.00 SP3 Standard Edition (32-bit) but the error persists. The exception.log files for both installs contains lines similar to the ...

Hide button based on a cell value
Hello, I have been trying to write a macro that would hide a button based on a cell value (cell N20). Somehow, it doesn't work. If N20=1, the button should be visible. If not, then it should be hidden. The button is called Button 4388 and the sheet is called Results. This is what I have come up with so far but as I said it doesn't work (please don't laught, I'm a beginner!): Sub HideButton() Dim myButton As Button With ActiveSheet Set myButton = .Buttons("Button 4388") If Range("N20").Value = "1" Then ..Visible = True E...

How do I change the size of Value Axis Title box for an Excel Cha.
The title box cannot be sized. I must reduce the font to very small to prevent truncating the title. I would have thought I could drag the box to the size I wanted but I do not get the chance to do so. Instead of a Title, you can use a Text Box: Select the Chart Type the text you want in the Title Press the Enter key A text box will appear in the centre of the chart Drag it to the location you want it, and format/resize as desired. If you prefer, you can link the text box to a worksheet cell, and display its text: Select the chart Type an equal sign Click on the cell you want to link Pr...

Making a query that creates a value base on a table value
Hi I have a table that has two fields (plus a KEY) CustomerName and BUYSELL I want to create a query that shows each record with either "RHS" if the value in BUYSELL = "BUY" or "LHS" if the value in BUYSELL = "SELL" It does not need to save this in a table just needs to show it on running of the query. How do I do this? Probably very easy but my knowledge of access isn't great. So basically now if I create a query to show all data it will show the customer names and either "BUY" or "SELL" next to it. I want th...

How does one update and transfer values between cells and not the. #2
I have a spreadsheet open that has several layers of formulas. I need to take a result of a formula in cell A and use that in the argument of another formula in cell B. Currently I have copied the contents of cell A to cell B and so the prior formula not the result is in the cell and as such the new argument can't update the changes to the value of cell B because between the two formulas I am multiplying and dividing by the same number. JC Just refer to the cell A in your cell B formula. In A1 you have =12*4 for a return of 48 In B1 enter =A1*2 for a return of 96 Gord Dibben...

Frequent Contacts Not Auto Filling
Unlike OE (or other email systems), I cannot get Windows Mail to auto populate the To: or CC: lines with email addresses that I frequently use unless I have used them in the past several days. This is very frustrating as the application claims to do this. I have checked settings and have it set up to do this, but it does not. Does anyone have any suggestions. Thanks much in advance. WinMail keeps a list of the last used 29 addresses (only) in the registry. If you want, you can clear all or some of them. How to clear the email auto-complete entries in Windows Mail http...

Counting Occurances (2003)
I have a cell that contains.... "2 red hat, 1 green glove, 2 red glove, 2 yellow hat" What I need to be able to do is; count the number of items containing "2 red" and return the value 4. And; count the number of items containing "2 yellow" and return the value 2. I've tried using COUNTIF, FIND, SEARCH, etc. and can't come up with a way to do it. Thank you in advance for your help! hi can you please put int in grid format so that i can figure it out what you want? "PeterM" wrote: > I have a cell that contains...

How to compare "varchar" values?
I need to compare a student's score (that s/he got it after a test) with the score requirement. The problem is the scores are either a "character" only, i.e., "2" OR combined a "character" AND the '+'/ '-' character, i.e., 2+. For example, if the require score is 2+ and the student's score is 2 then that student is not qualified. The datatype of scores is varchar. Can you please help in programming how to compare these values? Thanks a lot in advance. I appreciate it. I see two approaches. The first one is a trick, ...

Label XY scatter plot points by frequency of occurence (no pivot t
Hello, Thought I was done with my charting questions, but have one more... I have a spreadsheet with ~100 columns (individual animals, divided equally into 5 species) and ~100 rows (different morphological measurements). When I create my graphs, I have one graph for each measurement. The species are the x-axis (1, 2, 3, 4, 5) and the measurement range is the y-axis. Some of my measurements are categorical, such as color (black = 1, white = 2, yellow = 3, etc.). When I graph my 100 individuals, I see a point for each color for each species type. This is not surprising as there is a lot...

Generartion of Offline Address book error occurred
Dear, I dont know why my OAB isn=B4t generating Please if you know some causes let me know best regards I had a problem with this before, I had to tweak my=20 Global Address List query settings before it would work,=20 it doesnt like a email contact or public folder and its=20 messing up the offline. I uncheck Contacts and Public=20 folders on my query and it seemed to fix the problem.=20 Chris Webb MCP >-----Original Message----- >Dear, > >I dont know why my OAB isn=B4t generating > >Please if you know some causes let me know > >best regards >. > ...

How can I increase value in one cell as value in other decreases?
In Excel 2003, I would like to find out how to make the value in one cell decrease by 2.18% each time the value in another cell increases by 10%. Thanks. Liz Do you want to trigger the decrease if the reference cell increases by 10% in one update, or trigger the decrease if the reference cell grows by 10% over its baseline value in a series of updates? -- Gary''s Student - gsnu200758 "Liz Parkes" wrote: > > > In Excel 2003, I would like to find out how to make the value in one cell > decrease by 2.18% each time the value in another cell increase...

Displaying Trend Data which includes zero values
I am creating trend charts which looks at monthly data from the yea 2000. My data table is set up from April 2000 to March 2005. The chart are set up to read all this data, so that when the monthly data i updated the charts are automatically updated with all the values t date. However I don't want zero's displaying on my chart on the months tha havn't occurred, although zero may be a value within the data se itself. Is this possible? I know about using NA() but this doesn't work if zero was a value fo January 2003 for example, I would want zero to show here, but the grap to st...

Logical Filtering based on Value #2
I've been using these formulas to sort some values and return results I would like to combine these 2 into one formula =IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SUM(COUNTIF(C20368, {"DST","LVN","DNM"}))>0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<>"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),ISNUMBER(FIND("SHORT", G20368)), SUM(COUNTIF(C20368, {"BOG","BLM","CMO"}))),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND(LEFT (B...

Pickuplist type values from db
Hi, I created a custom field of type Pickuplist using Schema Manager, publish it and add it in an Account record type form. Question: How can I make its values loaded from another table (custom table in SQL Server database) instead of using the Values tab in the Form customization manually? Is it possible to load the values for this custom field dynamically? Please help. the sdk exposes classes to modify customizations...it may be possible to load values into picklists from a datasource using these classes...take a dig through the sdk -- John O'Donnell Microsoft CRM MVP http://www....

excluding repeating values
I have a column wuth date and some entries are repeating. I want to copy that data to another column, but do not want to copy duplicate values ( I want to exclude repeating values when copying and pasting). Is there an option to do that? Thanks. Neda Take a look at http://contextures.com/xladvfilter01.html#FilterUR In article <CE251167-21F0-470D-9641-508FD96D54FD@microsoft.com>, "neda5" <neda5@discussions.microsoft.com> wrote: > I have a column wuth date and some entries are repeating. I want to copy that > data to another column, but do not want to c...

How can I reference a value from a previous record?
Greetings forum members: I have a simple inspection data table that contains the following five fields: 1. Primary Key 2. Asset ID (Duplicates OK) 3. Inspection Date 4. Start Value 5. Finish value The data in this table must abide by the following rule: For each Asset ID; The start value of the current inspection record must be greater than or equal to the finish value of the previous inspection record. (The previous record would be determined using the Inspection Date field) My question is: How can I reference the value from the previous inspection record, in order to verify that th...

Finding the most frequent occurances
I have a worksheet in which the cells contain times followed by a 2 digit code. For example "2:30 AG". I was wondering if there is any way to find the time that occures most, ignoring the 2 digit code. The long term goal is to be able to determine at what times these occurances happen and create a graph that represents those peak times. You could try this: First, use a "helper" column to extract the time. With values starting in A1, enter this in B1: =--LEFT(A1,LEN(A1)-3) And copy down as needed. Then, format a cell to "Time", and enter this formula: =MODE(...

Comparing values of two charts
Thank you in advance for your assistance. I and trying to create a tool I can use to grade class assignments; one in particular uses charts. The class assignment the students are responsible for handing in will be one Excel file. I was hoping there was a way to sort of "extract" certain field values from the charts properties as well as the header/footer properties. If I were able to compare the one student's with my master copy it would save alot of time. If a more detailed explanation is required, I'll try to give you more. g ...