Count Number Of Consecutive Rows Of Equal Value?
Dec 27, 2013
I'm looking at test results and would like to know how many times in a row correct answers were given before getting one incorrect. The way I have this currently set up, is if a student has the answer correct it returns a "TRUE" value, and a "FALSE" value for an incorrect answer. A typical test may have 50 questions, so I would have either "TRUE" or "FALSE" values in rows A1 through A50.
So I've been trying to figure out a formula that counts the number of times "TRUE" rows repeat 'consecutively' in a given column, in this scenario column A. The formula would need to check the current row and if "TRUE" check the row immediately after, if that row is also "TRUE" then check the row immediately after that, until it runs into a "FALSE" string, then display that total count of consecutive "TRUES" in another cell. I understand there may be duplicate results but I'm only really interested in the longest value, so duplicates can be ignored.
If it makes the formula easier, each possible result can be assigned to it's own cell. Let's assume the test has 50 questions. So if a student got all 50 questions correct the result count would be 50 in cell B50, or if they got 25 correct before the first incorrect answer, cell B25 would say 25, etc. I'm guessing I could then easily query this column for the largest value to come up with what I need at a glance.
View 5 Replies
ADVERTISEMENT
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
Nov 18, 2008
I have an excel spreadsheet to record employee holiday and sickness figures.
It is set on as a grid e.g. column A stores all the dates and then employee names are used as column header.
One of the triggers I need to use is where, an employee has been absent 28 consecutive days. When an employee is absent I simply enter 'ABSENT' against there name.
Countif will count the number of time absent appears in the column however I need it to only recognise it if it is only 28 days in a row.
View 8 Replies
View Related
Nov 18, 2009
create a formula that counts consecutive rows that have wins and losses.
A B
1 Game 1 Win
2 Game 2 Win
3 Game 3 Win
4 Game 4 Lose
5 Game 5 Lose
6 Game 6 Win
7 Game 7 Lose
So Consecutive wins = 3
Consecutive losses = 2
View 9 Replies
View Related
Oct 11, 2012
What I am trying to is to count the number of times a certain number or character appears (either on its own or in a batch of consecutive cells containing that number/character) in a column.An example might clarify things (for reasons of brevity I will write the columns in rows):
If a column looks like (each 1-digit numbers / characters being a consecutive cell) 0 0 X X 0 0 X and I am counting for X, then I should get 2. If my column is X X 0 X X 0 X 0 0, then I should get 3. If my column is 0 X X 0 0 X 0 then I should get 2. If my column is X X 0 X X 0 X then I should get 3. Is there a formula to perform that calculation?
View 1 Replies
View Related
May 2, 2007
Let's say I have a column A with the following values.
30
40
30
60
-20
-10
-50
-60
-70
120
320
20
-40
-30
40
How can I have 2 cells display:-
i)highest streak of positive numbers = 4
ii)highest streak of negative numbers = 5?
Also, how can I have another 2 cells display:-
iii)the sum of the highest streak of positive numbers = 160
iv)the sum of the highest streak of negative numbers = -210
View 6 Replies
View Related
Jan 16, 2010
I'm trying to create a formula (in Sheet 1) that will return the most recent number of cells containing a value (i.e. not empty) in Sheet 2 Range AD2:A367 greater than zero.
I've tried using COUNTIF but that only adds the individual values, it doesn't 'start from zero again' when a zero value appears.
View 9 Replies
View Related
Jan 12, 2007
how to built a formula or a VBA code which would tell me how many times a range of a certain amount of consecutively stnading cells in a given row there is present, where each of the cells belonging to these five cells fulfills a certain criterion.
Example:
How many times does the range of 5 consecutive cells appear in range A1 to P2, where every cells belongng to these 5 consecutive cells would have a value graeater than 10.
View 8 Replies
View Related
May 27, 2014
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
View 9 Replies
View Related
Oct 23, 2012
I have a spreadsheet with two colums with dates. The names of the ranges are 'DataContractualStartDate' and 'DataContractualEndDate'.
How do I manage to count the no. of records with equal dates at the same row?
I have used the following but this gives me zero, while in the example below the result should be 3.
=COUNTIFS(DataContractualStartDate,DataContractualEndDate)
The table looks as follows:
dContractualStartDate
dContractualEndDate
01-05-2012
01-05-2012
[Code] ....
View 6 Replies
View Related
Sep 10, 2012
Say I have Column A populated with Employee Names, and Column B populated with "Pass" or "Fail" (which indicates a passed or failed customer service call monitoring).
For each employee, I want to be able to look at all of their overall scores (usually between 3 and 10 evaluations per month) and easily highlight people who have failed 2 out of 3 consecutive evaluations.
I want to integrate this feature in to a weekly quality report.
View 1 Replies
View Related
Jan 13, 2014
Need a formula for counting how many consecutive cells where the value is gretaer than or equal to +1
For example 1,
J18 = 7
K18 = 9
L18 = 3
M18 = 2
N18 = 8
O18 = 1
P18 = 8
Q18 = -17
In the above example the result would be 7
Example 2:
J12 = -22
K12 = 9
L12 = 4
In the above example the result would be 0 since the 1st value was -22
View 3 Replies
View Related
Feb 26, 2013
I am working on two worksheets. On my first worksheet I have one column with consecutive numbering starting from Cell 8.
A8 = 1
A9 = 2
A10 = 3
A11 = 4
A12 = 5
On my second worksheet I want to link to the values in the first worksheet but not in a consecutive manner. Each number will have an interval of 15 cells. As shown below:
D15 = 'link on Sheet1!A8'
D30 = 'link on Sheet1!A9'
D45 = 'link on Sheet1!A10'
D60 = 'link on Sheet1!A11'
D75 = 'link on Sheet1!A12'
View 6 Replies
View Related
Jun 29, 2008
I'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.
both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.
View 9 Replies
View Related
Mar 18, 2008
I need to count the maximum amount of consecutive crosses that appear in a list. For instance:
x
x
x
x
x
x
x
x
x
the result would be 4, because the highest amount of consecutive crosses in this list is 4 times.
Would anyone mind providing me with a formula for this, please? The list is from C5:C52.
View 10 Replies
View Related
Aug 10, 2009
ÁreaAplicação Tp ModeloData Produção
SPCContas Internacionais PDM01-01-2009
OPEDOL PDM02-06-2009
OPE PCOL PDM01-04-2009
OPESINTRA PDM12-03-2009
SPCSGF PDM04-05-2009
SCMControlo Vendas PDM21-02-2009
SCMClientes Ocasionais PDM03-04-2009
SPCContas Internacionais LDM14-01-2009
i need to count the number of rows that have the Tp Mpdelo="PDM" and The Date is lower of "31-03-2009".
View 9 Replies
View Related
Jun 24, 2007
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like:
1
1
1
0
1
0
0
0
1
0
0
1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
View 9 Replies
View Related
Apr 2, 2008
I have to calculate bonus payments for people working O/S. I have a 5 year calendar in month blocks (60 months/columns). In each month there is a percentage 'time O/S' figure.
If an employee is O/S for 3 consecutive months they get bonus 'A', 6 consective months bonus 'B', etc. How can I evaluate the 60 columns, returning the number of times 100% occurs in 3 consecutive months/columns. I have found examples of similar solutions but they will return a value of 3 if there are 5 consecutive months of 100%...
ie. 100 100 100 100 100
= 100 100 100 *** ***
& *** 100 100 100 ***
& *** *** 100 100 100
= 3
but I need it to equal 1
View 14 Replies
View Related
Dec 28, 2009
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
View 8 Replies
View Related
Feb 4, 2010
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?
View 11 Replies
View Related
Sep 22, 2011
I'm trying to come up with an array formula to count the consecutive declines or increases from the last value in a column, going from bottom to top. The real world application is a list of daily stock prices down a column, and counting from the most recent day, how many consecutive days the price declined or increased.
I got some hints in the following post but can't get it evaluate the resulting logical array in reverse order (which is needed since column addresses are evaluated top to bottom, not bottom to top).
Count consecutive increase in values
Example for consecutive increases leading to last value
49
50
51
52
53
49
55
56
I need a formula to return 2, the number of increases from 49 to 56.
View 5 Replies
View Related
Jul 25, 2009
I am a remodeling contractor trying to monitor trips my employees are making to the lumberyard. I am able to export to excel from our accounting program a column of dates that invoices are made and another column that tells me whether trips were made before 8:30, between 8:30 and 3:30, after 3:30, or whether materials were delivered involving no trip. So I can count "time of day" trips. I also want to know if multiple trips were made in a single day, or if trips were made 2, 3, 4, 5, etc. days in a row.
Column 1 Column 2
Monday, July 6 2009 Before 8:30
Monday, July 6 2009 Between 8:30 and 3:30
Tuesday, July 7 2009 Between 8:30 and 3:30
Wednesday, July 8 2009 Before 8:30
Friday, July 10 2009 After 3:30
Monday, July 13 2009 Before 8:30
Wednesday, July 15, 2009 Before 8:30
In this example, I have 2 trips made on a single day; 1 instance of trips made 3 days in a row; 1 instance of trips made 2 days in a row (Friday, and then again on Monday since we don't work weekends) and 1 instance of 1 trip made on an isolated day. Additionally, we have 4 trips made before 8:30; 2 trips between 8:30 and 3:30 and 1 trip made after 3:30. Columns for a single job could range to 30 or 40 trips or more, and we have 6-12 jobs running at any one time, so I could be looking at data for all of those jobs once a month if I can figure out how to make it easy to do. I have figured out how to count up the time of day trips (but included the example here for a fuller picture of what I'm trying to do) but cannot figure out how to count the number of 2,3,4,5 etc.-day-in-a-row trips that are being made.
View 9 Replies
View Related
Sep 22, 2006
I have a range of binary numbers and would like to count the number of times the value of 1 appears BUT there must be at least 4 consecutive 1's.
Here's an example:
1
1
1
1
1
1
0
1
0
1
1
1
The final count should be 6.
View 6 Replies
View Related
Jun 24, 2007
I have a daily column of numbers of approx 600 rows and the number is either a 0 or 1 and the 0 or 1 are in a random order in each row like;
1
1
1
0
1
0
0
0
1
0
0
1
I would like to find the min number of rows with 1, the max number of rows with 1, the totals of consecutive rows with 1 ie 3 consecutive rows of 1 appear 4 times, 4 consecutive rows appear 6 times etc and the average of the consecutive rows with 1.
View 5 Replies
View Related
Aug 13, 2008
I am preparing a attendance sheet. I am using 1 & 0 for present(=1) & absent(=0). I want to find out if a student has been absent for three consecutive days and if there is three consecutive 0 then the formula should return the value 0 ( the student gets 0 if he is absent for 3 consecutive days ) otherwise it should add all the 1s in the row. i.e
1 1 1 0 0 1 1 0 = 5
10 0 0 1 1 0 0 = 0
View 5 Replies
View Related
Dec 29, 2012
I want to be able to count the number of rows until I hit a blank row, and use the counter as an index. How is that possible?
I have a no. of rows that are fulled, followed by a blank row then another set of rows that are filled then a blank row etc. I want to count the rows filled till the blank row and set the counter to that number. Then I want to do the same with the next set of rows etc.
View 3 Replies
View Related
Mar 12, 2009
I would like to count items that appear in a list that will also appear in several rows. The list is also in no particular order. I have attached an example which may make it clearer than a wordy explanation.
View 2 Replies
View Related
Jul 11, 2009
I have a text box that I would like to populate with the total number of lines used by a listbox.
I tried:
View 8 Replies
View Related
Dec 11, 2005
I have in column AU cell 39 to cell 500 random sequences of 1-10 rows ,
with numbers in them, (as an result of formulas)
I need to count the number of rows in each sequence that have numbers
of 35 or less , but only up to the first number of 35 or higher. eg:-
AU result
row 39 32 3
40 14
41 19
42 107
43 11
44 21
45 #value!
46 40
47 45
48 16
49 15
50 #value!
51 29 2
52 11
53 146
54 14
55 17
56 11
57 #value!
and so on down to 500 rows
View 10 Replies
View Related
Oct 12, 2011
To count the genuine number of rows in a worksheet, I use...
count = Cells.Find(What:="*", SearchDirection:=xlPrevious,SearchOrder:=xlByRows).Row
When I add an auto-filter, the above still works fine.
When the auto-filter doesn't reduce the no. rows, the above returns a value of 1.
I've tried using...
count = Rows.SpecialCells(xlCellTypeVisible).Count
... with a result of 1 if no rows are filtered
So, what is the best way to count the populated number of rows on a worksheet where there is an auto-filter that may, or may not, reduce the number of rows ?
View 2 Replies
View Related