# Count Most Frequently Occuring

Dec 2, 2006
I have a list of numbers in an Excel range. Most of the numbers are the same but some are not. I need a function that will go through the list and return the value which occurs most frequently. (Not the number of occurences but the actual value). I need to do this in VBA.

View 3 Replies
ADVERTISEMENT
Dec 25, 2006

I know the mode function finds the most frequently occuring number but is there a way to find the most frequently occuring word/text?

View 3 Replies
View Related
Nov 21, 2006

I am given a database of 292 cells and i am asked to calculate the mode. How can i do it?

View 9 Replies
View Related
Mar 21, 2009

I need to know from the combinations below which are the 2 numbers that appeared the most. Example....

View 4 Replies
View Related
Aug 14, 2009

I have a column with over 60,000 rows of data. I need to find out which value in the table occurs the maximum number of times, and its count.

The traditional methods of COUNTIF or INDEX/MATCH using MODE dont seem to work and excel crashes after a few mins.

Is there any other way to do the same (other than splitting the file into several smaller files)?

View 9 Replies
View Related
Apr 26, 2007

I'd like to count the most frequently occuring value in a cell. That's it basically.

Say you have the following (actual extract) in a single cell

17,18,58,59,18,59,1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38, 39,40,41,42,43,44,45,46,47,48,49,50,16,18,23,49,54,59,62,18,59

What formula can I use to show that the most commonly occuring value appearing is 18? [Possible values are 10 through to 99].

The source data for this is in fact a single row accross 5 columns and I concatenated it thinking that made things easier.

The original:

B11: 17,18,58,59

C11: 18,59

D11: 1,2,3,4,5,6,7,8,9,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44, 45,46,47,48,49,50

E11: 16,18,23,49,54,59,62

F11: 18,59

Ideally the formula should take this (B11:F11) range as it's input (I can then spill it down 50-odd rows)

ps I have tried the following

=INDEX(B11:F11,MATCH(MAX(COUNTIF(B11:F11,B11:F11)),COUNTIF(B11:F11,B11:F11),0))

Unfortunately though the internet tells me this should work, the result I get back is 18,59 which is wrong because:

18 occurs 5 times

59 occurs only 4 times

View 9 Replies
View Related
Nov 29, 2006

I have an Array:

FALSE

TRUE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

TRUE

my aim is to count the maximum occurrences in sequence of False statements before they are interrupted with a True ( in this case 2)

I can figure it out what method to use, Formulas or VBA? More precisely my VBA sucks I am trying to use excel functions by creating a counter with if() and then sum it but obviously it is a dead end.

View 3 Replies
View Related
Dec 4, 2006

I need a probability distribution for amounts to be shipped taking into account both the probabilty distribution on parcel sizes (amounts to be shipped) per ship as well as the probabilities on the number of ships arriving.

Please see the following:

Say you have a probabilty distribution for ships arriving, i.e. the probabilty of 1 ship arriving in a specific time period= 0.25 ( event a), the probabilty of 2 ships arriving = 0.2 (event B) etc. Please take into account that this can go up to about 20 ships per time period.

Each ship can have one of four parcel sizes, (demand that needs to be met) (the following are just example probabilties)

0 tons with a probability of 0.67 event 1

1000 tons with a probabilty of 0.08 event 2

1500 tons with a probabilty of 0.222 event 3

2000 tons with a probabilty of 0.022 event 4

Thus event a has a probabilty of occuring, in event a, events 1to 4 can occur with their respective probabilties.

I assumed events 1 to 4 stay the same for all ships arriving (as we are dealing with multiple products, each product will have different parcel size distributions, however I look at products seperately, thus only one product is considered here)

This is simple for 1 or 2 ships, but as the number of ships increase the number of combinations become excessively large.

Should i use a macro that determines permutations/combinations? I am kind of clueless on this one.

If it helps to put the problem in context; I am trying to determine a demand distribution as input for a stochastic programming model

View 7 Replies
View Related
Dec 28, 2013

Formula to identify the top 10 most frequently occurring numbers in column A.

View 4 Replies
View Related
Feb 14, 2007

1. First thing I am trying to do. I have a column of cells that have multiple values, some with text and some with no values at all. I want to be able to display in A1 the most commonly occurring text in cells C1:C15, and be able to display in B1 the number of times that A1 occurs in the same range. Below are the formulas that I am using. There are two problems that I am running into: First, the formula returns a #NA error if any of the cells in the range are left blank. Second, the formula counts the spaces or zeros, so if there are more blanks than the word “amber” then A1 returns “ ” and B1 returns the corresponding number.

A1

=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))

B1

=COUNTIF(C1:C16,A1)

2. Second thing I am trying to do. In A2 I want to display the second most commonly occurring text in the range, with it’s corresponding count in cell B2, and the third most in A3 and B3, etc

Illustration:

C1 Amber

C2 Red

C3

C4

C5

C6 Red

Desired result:

A1 "Red" B1 "2"

A2 "Amber" B2 "1"

Results with forumla as posted

A1 " " B1 "3"

View 10 Replies
View Related
Dec 3, 2013

I have a list of data that is the day of the month(1-31) that an event has occurred. (sample data in attachment) I want to find some way to find when the "hottest" time of the month is when the events are occuring.

how to get the end result. I think for myself a chart of some sort would be good to see visually, or some kind of statistical breakdown.

My only requirement is that the list is always being added to, so the end result needs to be updating with the complete list.

Im not an expert in excel, so the more details on how to do this the better. Even better would be creating it in the attachment, and uploading your version so I can see exactly how you did it.

I am going to be taking this idea and apply it in the same way for day of week (1-7) and time of day.

View 3 Replies
View Related
Aug 16, 2012

I am using a formula that finds the most frequently occuring text from a range of cells that have the text 'Ra' 'Ma', 'Mo' or 'Mi' in them (each cell has a drop down list with these text items only). I am currently using this formula to find which of the four text items appears most frequently in the range:

=INDEX(D6:D15,MATCH(MAX(INDEX(COUNTIF(D6:D15,D6:D15),0)),INDEX(COUNTIF(D6:D15,D6:D15),0),0))

This formula works fine in a case where there is more of one text item than any other, but when there are two items with the same number of entries, this formula comes back with the item that fulfils the criteria first - i.e. if 'Ra' appears 5 times and 'Mo' appears 5 times, then either could come back, depending on which appears first in the range.

The text items rate in importance with 'Ra' being the most important, then 'Ma', then 'Mo', then 'Mi' is the least important. What I am trying to do is, in cases where more that one text item appears the same number of times, get the formula to display the one that is the most important. So for example 'Ra' will always appear over 'Mo' if both have 5 entries, but 'Mo' will appear over 'Mi' if 'Mo' and 'Mi' both have the most entries.

View 2 Replies
View Related
Feb 5, 2012

I have 12,000 rows on my excel spread sheet of data and my average volume for all those days are 17,661,398.6

I noticed that the so called average did not show up not even once in all of the 12,000 rows. Thus I conclude that the average is not normal ? How to find & highlight the most frequently occurring number, the usual, and or the normal numbers?

View 9 Replies
View Related
Dec 9, 2006

I am useing Excel 2003 and OPC server client for excel, and i am monitoring 6 values, that are changeing each second. I was trying to make a function to collect this information, so i could form a statistic table, but unfortunally with no success.

I can watch how the values are changeing their numbers, but i cant sotre the values in the rows below. Does anyone know how can i do this

View 9 Replies
View Related
May 9, 2008

I have a few columns referencing Crash numbers. The majority of each column is a null value, with about 10-20% of it actually containing the number of a Crash. I want to run one MODE each on a column to see what the most common Crash is.

The following

=MODE(Table_X[Crash Alpha])

Results in a #N/A Value Not Available error. What do I need to change to make it work on the column with some nulls and some numbers (no strings)?

As a secondary question, if I wanted to apply a date filter (the columns are part of a table, including a Date column) like the below:

If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, etc.)

(F9 being the location where my filter Date is)

Where would I apply that to the Mode? Should I just change it to an If statement, with the Mode positioned like so?

=If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, MODE(Table_X[Crash Alpha]))

View 9 Replies
View Related
Jan 8, 2014

I have a horizontal bar chart in which some of the bars represent positive values and others represent negative values.

How to format the bars so the "positive" bars are shaded in one colour and "negative" bars in another colour? I'd prefer not to change the bar colour manually as the values change frequently.

View 2 Replies
View Related
Mar 17, 2014

I'd like to create a macro to display the top 5 most frequently occurring repairs on sheet 3 of my workbook. I'd like the macro to analyse all the sheets in my workbook except for the first 3 and then output the results on the 3rd sheet from T50 onward. I have attached a template as an example of how all the sheets in the workbook look (excluding the first 3).

All the sheets have the same layout as the "Survey Template" worksheet.The repairs are located under the Log Book Review of Historical Structural Repair in the Survey Template worksheet and all the repairs are picked through a drop down list that is based on a table of repairs listed in the Parts and Prices sheet within the workbook. I have assigned random serial numbers to the repairs (if that makes it any easier to code instead of locating exact string matches).

ExampleSurveyTemplate.xlsxâ€Ž

View 2 Replies
View Related
Mar 7, 2008

I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).

For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.

So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)

View 2 Replies
View Related
Jul 4, 2012

I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.

I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.

This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).

View 2 Replies
View Related
Nov 17, 2007

see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.

View 2 Replies
View Related
Aug 7, 2013

I need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.

Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.

Examples of the contents of cells in col D with the "Category:" prefix are as follows:

Category: Adversity

Category: Answers

Category: Assurance

Category: Blessings

Category: Build

Category: Change

Category: Children

Category: Choices

Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.

I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.

View 9 Replies
View Related
Jan 20, 2008

I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.

But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.

E.g

Large Parts Current Stock = 50

(enter in) Large Parts Used = 2

Large Parts Current Stock = 48

(Clear field where 2 was entered into Large Parts used)

(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)

View 9 Replies
View Related
Aug 21, 2006

going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....

View 6 Replies
View Related
Apr 15, 2014

Column A has current building, column b has future building. Would like to count the number of changes without adding a separate column with an if statement.

View 3 Replies
View Related
Mar 26, 2009

I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.

The attached example explains things a lot better.

I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.

View 2 Replies
View Related
Jun 22, 2009

I want to count from each cell that doesn't contain "0". So if cell C2=100, I want to be able to count the number g1*2 from that cell and return a value. But then I want to start another count from c5 to the number of g1*2 and then another count from c8 etc basically any cell that contains a value other than "0", I want to start a count from.

The point of this is that the half life will expire after that count, so I want to be able to add the drug levels on an ongoing basis until the count of the half life has been reached. But there will be further dosing along the way before this half life is reached and these values need to be added to the existing value until the half life expires.

View 2 Replies
View Related
Feb 5, 2010

I want to count cells in column AA that are graeter than 160, and in column N = "RM" and in column A = "CBP". Can't seem to get this right.

View 4 Replies
View Related
Jul 1, 2014

VBA which would count data in Column F of dump Sheet and paste the count in master sheet B2 Cell.

View 7 Replies
View Related
Sep 23, 2005

I require a Formula to calculate the INTERVALS (the number of Rows between

the LAST instance and the PREVIOUS instance in a column) between each

individual occurrence of any designated PAIR of Numeric values (single-digit

/ double-digit) in the same Row of the Named Range "Results" and return each

calculated INTERVAL result to a separate Column on the same Row of a New

Sheet - starting with the most recent ( the LAST) occurrence.

For instance, each time 80 and 87 appear together in the same Row, return the

INTERVAL by calculating the number of Rows between the LAST instance and the

PREVIOUS instance in a column - locate when both Numeric values LAST appeared

together and Count back to their PREVIOUS appearance together to get the

required Count; i.e. count from the Row ABOVE LAST appearance to the Row

BEFORE PREVIOUS appearance.

The results are returned to a chart / matrix layout: I have the criterion

vertically and horizontally and they are referenced using the horizontal and

vertical cell address that houses each criterion, and the results are

returned across the Row of the intercept of the vertical and horizontal

criterion. At some point both criterion values being referenced will be the

same, can the Formula return empty text "" when this occurs?

Example Chart / Matrix Layout:

Cell Ref. A2 and B1 criterion 80 and 80

Cell Ref. A3 and B1 criterion 81 and 80

Cell Ref. A4 and B1 criterion 82 and 80

Criteria B1 houses 80

A2 houses 80

A3 houses 81

A4 houses 82

A5 houses 83

View 12 Replies
View Related
Mar 7, 2012

ABCDE1DATEEMP1Days Between SalesEMP2Days Between Sales

23/6/2012 YES0NOÂ 33/5/2012NOÂ NOÂ 43/4/2012NOÂ NOÂ 53/3/2012 YES2NOÂ 63/2/2012NOÂ NOÂ 7 3/1/2012 YES1YES682/29/2012 YES0NOÂ 92/28/2012NOÂ NOÂ 102/27/2012 YES1NOÂ 112/26/2012 YES0NOÂ 122/25/2012NOÂ YES4

I believe I need a loop code to do what I need, because none of the functions I've tried have worked. I want to start at B2 and go down the column until I come to a YES. When I find a YES, I want to know the number of NOs that preceded it. Then I want to go from that YES(#1) to the next YES(#2) and count the number of NOs between YES(#1) and YES(#2) and so forth, until I run out of rows. For example, in C5, the answer is 2, because there are 2 NOs between YES#1 and YES#2 in coulmn B, and a 1 in C7, because there is 1 NO between YES(#2) and YES(#3) in column B.

View 5 Replies
View Related