Number Of Consecutive Days And First Occurrence
Nov 24, 2009
In my worksheet, I have a column of ascending dates from BD7 to BD22, with BD22 being the current date. In column BN, I track whether a process is "Over" or "Under" a limit.
The values in column BN are text with either of those names.
I need to return two values in cells BO7 and BO8 respectively:
1) The number of consecutive days back from the most current day that have been "Over" the limit
2) The date going from the most current day that the first "Over" occurred
View 9 Replies
ADVERTISEMENT
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
Jun 15, 2009
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like
Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
View 3 Replies
View Related
Dec 24, 2013
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
View 2 Replies
View Related
Jun 27, 2006
I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
View 5 Replies
View Related
Feb 16, 2014
How to find five consecutive working days for the staffs who worked only in night shift basis and if this comes true then to find other night shift for particular staff based on Bank ID and putting those data's in new excel sheet believe this can done through a macro.
Its new assignment to me to find the staffs to make the inconvenience allowance for the staffs who worked in night shift for five consecutive days and if they worked for five consecutive days then they eligible for other night shift days they have worked.
Given the data how it looks in our attendance. In the below data night shift criteria true stands for staffs who worked in night shift and false stands for staffs who worked in day shift.
Serial No
Bank ID
Name
Date
Attendance Type
Start Time
End Time
NIGHT SHIFT
1
1374058
A
17-Jan-14
Login
10:00 AM
7:30 AM
FALSE
[Code] ........
View 1 Replies
View Related
Apr 11, 2008
the total consecutive days at the end of the month that a particular person appears?
Here's the sample:
April 1: A
April 1: B
April 1: C
April 2: A
April 2: D
April 2: E
April 3: A
April 3: C
April 3: E
Based on the sample above, the result should be 5 (as A=3 and E=2).
View 9 Replies
View Related
Aug 7, 2013
I have a range of data but the important pieces are
Column B - list of employee IDs.
Column C - Date
I have sorted the data by column B with secondary sort for column C oldest to newest date.
I want to know the number of instances where an employee has 3 consecutive days in column C. Only count 3 consecutive. If the consecutive days go over 3 (such as 4 or 5 days) I do no want to count these.
View 8 Replies
View Related
May 7, 2012
I am using Excel 2010. I am tracking client orders on a spreadsheet. I would like to offer a 60 percent rebate on revenue from orders placed between the first order date and 30 calendar days thereafter. On day 31 and continuing through day 60, I would like to offer a 40% rebate. On orders placed on day 61 through 90, I would like to offer a rebate of 20%. From all of the orders placed by clients who start ordering on different dates, I need to sum their order revenue in these three time periods. I have the individual client order data arrayed on my spreadsheet - one order per row.
I am capturing the unique client ID number (Column A), the order date (Column B), and the associated revenue (Column C). Keying off of their unique ID number, I would like to calculate the sum of how much revenue is generated in calendar days 1 through 30, days 31 through 60, and days 61 through 90 from the very first order date from any given client. The start date can be any date in the month, so I can not rely on any calculation that uses the calendar month. Orders are not necessarily placed every day. Multiple orders may be placed on any given day, and then no orders may follow for many days afterward.
View 1 Replies
View Related
Mar 14, 2014
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
View 9 Replies
View Related
Dec 2, 2008
I have an worksheet where I use sequential numbers in column B but it often happens that we insert some more rows with the same number in the B column.
Ex.:
01
02
03
04
04
04
04
05
06
06
Now we would like to have a macro that will format the text color (change to white) in columns B & D as soon as there is a second (or more) occurrence(s) of the same preceding number.
Ex.:
01 – Black (unchanged)
02 – Black (unchanged)
03 – Black (unchanged)
04 – Black (unchanged)
04 – White (changed)
04 – White (changed)
04 – White (changed)
05 – Black (unchanged)
06 – Black (unchanged)
06 – White (changed)
View 9 Replies
View Related
Dec 15, 2008
What Formula will return the above request (Preferable as "simple" as possible) ...
View 13 Replies
View Related
Aug 15, 2008
I've found Nth_Occurrence and it's frankly brilliant but I need to be able to return the occurrence number of a value in a range and it doesn't quite do what I need.
Using = COUNTIF(A$1:A1,A1) gives me exactly the answer I need but as I'm working with big ranges it's taking a lot of calculation time.
View 9 Replies
View Related
Sep 25, 2013
how to search a defined range (using namned range and offset to last non empty row) and register row number for the first occurance of each ID using VBA. I made an example file where I need to search column A (search range using namned range "ID" and offset to last non empty row) and register rownumber for the first occurance of each ID in Column C.
example.xlsm
View 1 Replies
View Related
Aug 14, 2014
I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.
E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80
and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.
I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.
View 12 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
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
Nov 22, 2013
I want to calculate the end date of my German courses. This is how it works:
A course consists of 60 LU*. The course can occur i. e. three times a week: Monday, Wednesday and Friday. In each day the course lasts 2 LU, which means 6 LU each week. There is no course on Tuesday, Thursday, Saturday, Sunday and on holidays. Therefore this type of course that begins on 18-Nov-2013 will end on 03-Feb-2014.
Another course which occurs Tuesday, Thursday and Saturday, and respectively has 2 LU on Tuesday, 2 LU on Thursday and 3 LU on Saturday and starts on 03-Dec-2013 will end on 06-Feb-2014.
Therefore I want to create a worksheet where I set the start date, choose the days and respectively the LU amount on those days. The end date shall be calculated according to these criteria.
The workday function on excel cannot do this and I do not have any programming skills to work with VBA.
Legend:
*LU = lesson units; 1 LU is 45 minutes
Holidays:
28-Nov-13
29-Nov-13
08-Dec-13
09-Dec-13
25-Dec-13
31-Dec-13
01-Jan-14
[Code] ...........
View 7 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
Oct 9, 2009
In cells v43:v52 I have data which will change often the data is always either a 1,-1 or a 0, I need a way to count from the bottom up and ignore anything that is not part of the chain. It can also contain 0 in rows that are not populated yet but the 0s will always be on the bottom and never in the middle or top.
I.E. in 3 weeks it could be
1
1
1
0
0
0
0
0
0
0
0
0
I would want the formula to spit out 3 because it was 1 3 times in a row..
Now say in week 5 it is
1
1
1..............................
View 7 Replies
View Related
Jan 4, 2014
I have a spread sheet of hourly temperatures which is broken down into year, month, day, time, and temperatures. I would like to find the top ten longest stretches of sub-zero temperatures in number of hours and the time and date in which they started and ended. I have attached a spreadsheet for your perusal..
View 3 Replies
View Related
May 5, 2009
E20 with the series of numbers in tenth, and with five numbers on one cell. Between 01-37. How do I write a formula on column R to identify any repeated numbers from E20 with E21? In the chart it shows number 10 is repeated from E21, so I place a check mark on the same row as E20 on column R.
On column S how do I write a formula to identify numbers within E20 to see if there are any consecutive numbers? On E20 there is none, on E21 there is one 10-11, and column S will let me know if there is a consecutive by placing a check mark.
View 9 Replies
View Related
Jan 13, 2007
to create with the default excel functions the following calculator. I need to calculate the maximum number of positive numbers which happen in a row and the maximum number of consecutive negative numbers. For example in the following list of numbers there are a maximum of 7 consecutive positive and a maximum of 6 consecutive negative numbers:
6 000
6 360
6 742
7 146
7 196
7 628
8 086
-4 071
7 898
-4 186
8 121
8 608
-4 562
to make a formula which will calculate the maximum length of positive and negative numbers in a row? I attached this table to the post.
View 9 Replies
View Related
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
View Related
Apr 10, 2008
i've got a sample database (attached) with a userform for inputting of data. What i want it to do is automatically generate the next number and add it to textbox "our ref" on the userform. the number is in Col C. This is the code i've tried using the code highlighted in red which doesnt work.
View 12 Replies
View Related
Mar 22, 2014
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
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
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
View Related
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
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
