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 RepliesBasically 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").
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.
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.
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..............................
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.
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
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.
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.
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 RelatedWhat 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?
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 RelatedUsing 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.
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
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.
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.
I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.
Sub HTSoutput1()
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"
Sheets("Nuclei").Select
Range("B10:B17").Select.............
As you can see on the example i have TEST hours.xlsx, I have a file that calculate the money every doctor should take based on the working hours.
Nights, holidays and holiday night have different price/hour.
The excel is working fine…but now I have to make a formula that separates automatically based on the beginning time and the end time of the doctor’s shift the day hours tha night hours, the holiday hours and the holiday nights hours.
In the excel I have fill the hours Manuscript, I need a formula to do that for me…
On the yellow cell I have try to find out the formula for the holiday hours but because the day is calculated due to a formula it is not working!!!
Simple Example: A doctor Is working from 21:00- 8:00 (next morning Sunday) he should have 1 simple hour (21:00-22:00) 9 night hours (22:00-6:00) and 2 holiday hours (6:00-8:00,)
Cell A1 contains 2, B1=220077:23
In C1 I created a function = (2*1000)/B1, I get error. I want the answer 2000/220077h23m = 0.009087713
This is for a trucking company and I want to calculate how many hours an asset has been idol. I have the dates an times of use for each asset on a tracking sheet. Each row has a different instance on when the item has been utilized. I want another column to tell me how many house have elapsed between the current "time in" from the last time out.
I have attached a file with the column I would like to add to populate itself
Hours Idol.xlsx
I suspect it will be a match and index formula
Is there a way to calculate the number of hours between two times specified like this:
Start
End
Hours
Comments
Mon 6:00p
Mon 9:00p
3.00
Same day
[Code]..
I think I can write a UDF and that may be the easiest. Is there a straightforward way without a UDF?
I'm trying to create a userform which calculates and records the total number of hours a user puts in, however it can not exceed 40 hrs.
Dim intProject1 As Integer
Dim intProject2 As Integer
Dim intProject3 As Integer
Dim intTotal As Integer
intProject1 = txtProject1.Text
intProject2 = txtProject2.Text
intProject3 = txtProject3.Text
intTotal = txtTotal.Text
If txtTotal > 40 Then
txtTotal = txtProject1.Text + _
txtProject2.Text txtProject3.Text
txtTotal.Text = Format(txtTotal, "currency")
Else
MsgBox ["Total exceeds 40 hours."]
End If
If you guys could help me out I appreciate it
Thanks
End Sub
Private Sub lblProject1_Click()
How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?
A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.
I'm trying to do a simple calculation of the # of hours between 2 times, rounded up to the nearest hours. Somehow along the way I've ended up with this massive formula below, which is doing what it's supposed to do except for certain cases (like the one in the attached file...where it says the difference between 2am and 3am is 2 hours) =ROUNDUP(IF(B2="",0,IF(A2="",0,(IF(B2<A2,((((B2+1)-A2)*1440)/60),(((B2-A2)*1440)/60))))),0)
View 4 Replies View RelatedI'm hoping someone here might be able to help me please?
I am trying to write a function that will convert a number into days-hours-minutes. I have managed to get as far as hours-minutes using the following function.
=INT(A1/60)&"h "&ROUND(MOD(A1/60,1)*60,0)&"m"
e.g. If A1 = "7090" the result of this function will be "118h 10m".
I now need to express the result as xxxd xxxh xxxm and this is where I am stuck!
I have textbox1, which is updated with a date and time value formated as DD:MM:YY HH:MM and textbox2 value hich is also updated as DD:MM:YY HH:MM. I want textbox3 to deduct 2 from 1 but it won't do it I have tried:
View 5 Replies View Relatedyou must have the Analysis TookPack installed to use this function. It's comes with Excel. Look under Tools>Add Ins.
View 9 Replies View RelatedI need to create a formula that assumes 40 hours = 1 FTE (full time employee). As an example if I have a total of 100 hrs I need to know how many employees to hire. So in this case it would be 2.5.
View 1 Replies View RelatedI'm working with a spreadsheet that works out how many hours someone has worked on a given shift.
In cell C4 I have the formula =E4-D4+IF(D4>E4,1)
In cells D4 & D5 I have an entered number in time format (custom hh:mm)
The problem with this is that when a time is entered in either D4 or E4 it automatically gives me a number of hours worked (upto midnight) without any input in the other.
Aim: Not to show the number of hours worked in cell C4 until hours in both cells D4 & E4 have been entered. NB. Sometimes the start times are 20:00 until 08:00 (night shift) hence answer 10:00.
how would I calculate the number of hours in a shift after midnight. What I want to say is that the number of hours after midnight on a friday shift to be taken off from friday total hours and added to a saturday shift.