Return Number Of Cells With The Most Consecutive Specific Values?
Aug 13, 2014
I have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
View 8 Replies
ADVERTISEMENT
Jul 5, 2006
Using a Dynamic Named Range "Data", I would like a Formula to return the Row
Number of the "LAST" instance of a duplicate numeric value - repeating twice
consecutively in the same column.
View 10 Replies
View Related
Oct 25, 2007
Basically I want to calculate each team's home winning/drawing/losing streak. Here is a sample of the table (I've simplified it a bit and added fictional results):
(See Attached)
So, I want a formula that can tell me a given team's (e.g. Arsenal's) latest result, and how many previous consecutive results of that kind there have been. For example, for Arsenal, the result would be something like "W2" (i.e. Won the last two games). At the moment, the best I can do is to get the TOTAL NUMBER of a given team's latest result using this formula:
=LOOKUP("ARS",B:B,C:C)&SUMPRODUCT(--(B1:B13="ARS")*(C1:C13=LOOKUP("ARS",B:B,C:C)))
(Which also returns "W2").
View 4 Replies
View Related
Mar 27, 2014
I wanted to count the number of instances that Matt's been late. If there are consecutive timestamps (ex. 600-620, 620-640) that he's been late, I wanted Excel to display 1 and then I'll just sum it up. Or if Excel can do this directly, add all the instances because what I actually need is the total per person.
In this example, I would need a result of 4 instances.
tell if the blank cells will affect the formula or if I still need to do something about them.
View 3 Replies
View Related
Jan 30, 2014
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
View 14 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
Dec 4, 2013
1.jpg
I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)
View 5 Replies
View Related
Apr 29, 2014
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
View 5 Replies
View Related
Jun 14, 2008
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
View 5 Replies
View Related
Aug 14, 2009
Sheet1
BCDEFGHIJ2Product10/08/200917/08/200924/08/200931/08/200907/09/200914/09/200921/09/200928/09/20093A228157989393994B1784371107922385C4483398261701298
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
And I am after a formula that will return the total sum of values between two specific dates.
So if my results table looks like the one below, the values the formula would return are shown in Cells E9:E11.
Sheet1
BCDE8ProductStart DateEnd DateTotal9A24/08/200914/09/200934110B10/08/200907/09/200918511C31/08/200921/09/2009225
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View 9 Replies
View Related
Apr 17, 2009
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View 7 Replies
View Related
Apr 28, 2006
on one sheet we have a summary of the main list, which includes totals of money recieved, totals of all the different sources (ie, where they heard about us from), the totals of the frequencies they pay (ie, how many donate monthly, quarterly...) ... etc. on the next sheet we have the "main" list of donors, their IDs, amounts, frequency, source ...
the totals on the first sheet are updated manually, but i want to change that as there are a great number of errors.
View 8 Replies
View Related
Nov 6, 2013
Having an issue with an array formula.
{IFERROR(INDEX('sheet2'!$A$2:$A$14,SMALL(IF('sheet2'!$C$2:$C$14="X",
ROW('sheet2'!$A$2:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.
Originally I did not need to skip any rows, but now I have to skip certain rows. I've tried using my current array formula and skipping the rows not needed but can't get it to work.
{IFERROR(INDEX('sheet2'!$A$2:$A$5,$A$9:$A$14:SMALL(IF('sheet2'!$C$2:$C$5,$C$9:$C$14="X",
ROW('sheet2'!$A$2:$A$5,$A$9:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}.
Can this still be done with an array formula? I need to skip rows 6, 7, 8. Not sure what I'm doing wrong but no names are appearing. Sheet 1 looks like this with "Job Titles" starting in A2 on sheet1.
Job Titles
Dept.
1
2
3
4
Claims
CSI
Outb Calls
CSI
Notifications
CSI
Evaluator
CSI
I tried a new formula in cell C3
:=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)=9,
IF(Sheet2!$C$2:$C$14="x",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"") entered as an array.
I should see the name "C. Pepper" followed by "L. Smith" when I copy the formula to the right into D3, "J. Rast" would be skipped as these are the rows not being picked up, but nothing is showing.
Sheet 2 looks like this with "Staff Name" starting in cell A1:
Staff Name
Mgr
Claims
Outb Calls
Notifications
Evaluator
A. Hawkins
Adams
[Code] ..........
View 3 Replies
View Related
Oct 30, 2013
In the attached sample file, one can see the setup of my data. I'm trying to reference the row number of a certain cell in a matrix with many cells of the same value. I have different Headers of each row, which should make it possible to use INDEX, MATCH or VLOOKUP. but i don't seem to be able to get it right.
Description.I need to return the "class" och each of the capability elements, i.e. If the capability in "Soups" is marked ("X"), at class 3, i need the integer "3" to be shown here.
View 12 Replies
View Related
Feb 20, 2008
I have the following formula: =IF(C319<=300,9,IF(AND(C319>300,C319<500,6.75),IF(C319>500,6))) It returns 9 if C319<=300 but returns FALSE for all other numbers. I want the funtion to return 9 If <=300 and 6.75 if C319 is greater than 300 but <=500 and if it greater than 500 it should return 6.
View 5 Replies
View Related
Jan 31, 2013
I have a similar question regarding coloring of empty cells between two cells with specific numbers as posted in below thread : [URL] .....
This is exactly what I want. But here I have some problem with formula.
=COUNTIF(A1:Z1,1)=1
Above formula could pick cell value 1 and color the empty cells in a row between the cells having value=1. But if I want to use the same formatting for other numbers like 2 or 3 it doesn't work.
For example in row(A1:Z1) I have A1=1, D1=1, and F1=2, K1=2, and O1=3, U1=3
I required multiple conditional formatting that could color the cells based on values first from A to D then from F to K and finally from O to U.
I'm using above formatting it works with cells having values 1 but its not working for 2, 3 or any other cells values.
I have already tried replacing 1 in above formula with 2 and 3 but it doesn't work.
View 1 Replies
View Related
Feb 1, 2010
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View 3 Replies
View Related
May 12, 2013
I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.
Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6
Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0
The formula I am using is:
=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5
Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.
Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.
My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?
View 2 Replies
View Related
May 5, 2008
I will say exactly what i want.
I want to keep statistics of roulette.
More specifically, i want to count how many times a dozen lates to come.
The range from 1-12 is 1st dozen, from 13-24 is the 2nd and 25-36 is the 3rd.
I want to count among these numbers, how many times one of three dozens appear every 1 time, how many times appears every 2 times, .... until 20 times.
For example: I have put in a column 500 numbers from 0 to 36.
The output must be like this:
1st Dozen: Every 1 time: x
Every 2 times: n
Every 20 times: m
The same for 2nd and 3rd dozen.
View 9 Replies
View Related
Apr 9, 2014
I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.
View 8 Replies
View Related
Sep 25, 2013
Any method for colour highlighting a row (specific number of cells) as and when any cell of that row is selected, and return to normal when deselected.
View 4 Replies
View Related
May 11, 2009
I have 10 columns (A-J). Column B is the Date & Column C is the Time, I have the data sorted 1st by date then by time. The 5th Column, E, is Size. The 10th column, J, is "Position."
This 10th column, J, is in binary format. A 1 equals a Position & a 0 equals no Position.
1) I need to find the Max Sum of the Size in which the Max consecutive set of 1s has occurred.
2) I need to also do this on a day by day basis, using the Date in Column B, without having to manually go through & modify all the formulas to reference the date.
I have a Sumif formula for the 2nd but it won't copy down to update the formula for each unique date. I have to manually change the date values & I have ~957 unique dates to do; so it would be a rather painstaking process if only done manually. Moreover, it doesn't count the Max Sum of all consecutive 1s in Column J...
The length of the non unique columns (all columns w/o unselecting the duplicate entries) is 19,068.
If you can be of help, I'd be very thankful...my brain is overloaded w/googling & using trial & error.
View 6 Replies
View Related
Apr 5, 2008
I've created a userform that has one ComboBox (ComboBox1) and two text fields. I am trying to get the userform to return information to my worksheet in the same row as the name that is displayed in the ComboBox. This is my VBA code.
Private Sub Cmdpayment_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Sheet4
iRow = Cells. Find(What:=Me.ComboBox1.Value, After:=C5, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ws.Cells(iRow, 12).Value = Me.txtpdate.Value
ws.Cells(iRow, 13).Value = Me.txtpayment.Value
Me.txtpdate.Value = ""
Me.txtpayment.Value = ""
End Sub
View 8 Replies
View Related
Apr 23, 2013
Thing is, I have an excel sheet for a private competition in sports games. How can I do a function, that automatically calcutes points for every "bet" against the game result?
For example:
If game finishes 4-3 I'd like to compare it for every players guessed score and give points according to this:
1. If the bet is completely correct (4-3) -> Return 10 (points). In case of tie (like 3-3, -> Return 20 (points)
2. If the bet has a correct winner, with correct goals for eather team (4-x / x-3) -> Return 4 (points)
3. If the bet has a correct winner (home/visitor) -> Return 3 (points)
4. If the bet has incorrect winner but amount of goals for eather team correct -> Return 1 (point)
5. If the bet is completely wrong -> Return 0 (points)
At least for me, this sounds more like rocket science but just wanted to check if this is anyway doable.
View 9 Replies
View Related
Mar 25, 2009
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
View 9 Replies
View Related
May 22, 2014
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
View 4 Replies
View Related
May 8, 2014
i have a question regarding the import of csv-files. I've seen lots of working import-functions/macros but none of them. So here are my questions:
1) is it possible to import data from a csv-file into specific cells in my worksheet? Let's assume i have a csv-file with 10 INT-Values and they all should be placed in different rows/colums with no regular pattern.
2) If the answer of "1)" equals "yes": how is it possible?
(i thought about importing the csv in an so called "helper-worksheet" and use formula/references inside the table, but to be honest: That's not the solution i like to have)
View 5 Replies
View Related
Jan 16, 2010
I'm new here and not really that familiar with excel functions and programming so please forgive me if this is not the right place for my thread
I am wondering how (if possible) I can make this little scenario work:
Lets say I have
cell (A1) with the value 2
cell (B1) with the value 3
Now somewhere else I wish to color some cell to create a 2 by 3 "block".
So coloring 2 cells horizontically (the value of (A1) and 3 cells Vertically (value of (B1)
View 12 Replies
View Related
Nov 26, 2012
I have excel sheets with 71936 rows of data collected ~30 times a second for 40 minutes during and driving simulator drive (for 51 participants! all in separate worksheets). The output I get from the simulator includes speed, lane position etc. I've been averaging the data out into minute blocks for most variables, but what I really need to know is: if they exit the "lane" for more than 3 seconds, and if so, how many times they exit the lane in each minute.
Column A is the minute, column B is their lane position. Lane position ranges from -540 to 540. The middle of the left hand lane is -180 (the left hand lane ranges from -360 to 0 - we drive on the left in Australia). If they exit the left hand side of the lane, the lane position value will be between -360 and-540, and if they exit the right hand side the lane position value is 0 or greater.
A formula in a new column that can give a "1" each time they exit the lane for more than 3 seconds would be great, then I think I can make it sum how many times they do it in each minute.
View 6 Replies
View Related
