Forum Discussion
Excel: Help! Formula needed
Hi there, I'm a biological research masters student and I have a large data set. I'm working with seals so it is difficult to identify individuals. I'm trying to create a spreadsheet ready for da...
Does this return the intended result?
Formula in cell E2 in the example and filled down:
=INDEX($C$2:$C$10000,IF(MOD(ROW(A1),5)=0,QUOTIENT(ROW(A1),5),QUOTIENT(ROW(A1),5)+1))Formula in cell C2 in the example and filled down:
=ROW(A1)/2880+1/17280
So to answer mathetes questions:
My apologies, it's a hard one to define for me as I'm neurodivergent.
So I think the "pattern" is dependent on how many individuals are within that pool during that day. So for example the max number of seals in pool 1 for the 16/2/2023 is 5. So there will be 5 replicates of the time signature e.g. 12:23:35, before going into the next time signature where there will be another 5 replicates of the same time signature 12:24:05. The start time varies because of malfunctions and other issues in regards to the cameras. So for example this particular clip for the day starts at 13:13:05 and ends at 15:38:35. There is no consistency with the cameras in regards to the time they start the Timelapse so this is near enough always going to be different. However, the times between the start and end will always be 30s as that was what the interval for the Timelapse was set to.
So. OliverScheurich has the wanted outcome however, I need it to duplicate the same time for every 5 rows for example. And then for other days, in other pools I have 12 individuals, so there would need to be 12 replicates of the same time.
I guess what I'm trying to do is to find a way to repeat the same times for x rows (dependent on the number of individuals) and then have the time change for 30s later and then repeat for another x rows for the same day to an unspecified time point. I hope that makes sense!
=INDEX($C$2:$C$10000,IF(MOD(ROW(A1),12)=0,QUOTIENT(ROW(A1),12),QUOTIENT(ROW(A1),12)+1))
This formula duplicates the results in column E for every 12 rows. I've only changed 5 to 12 compared to the already suggested formula.
=ROW(A1)/2880+18006/86400
With this formula you can change the start time to 05:00:36. The formula is in cell C2. The first part ROW(A1)/2880 represents 30 seconds and 18006/86400 represents 05:00:06 because this is the 18006th second of the day. You can fill this formula down until the actual end time for that day.
- Okay great thanks. I just have a question regarding the 18006/86400. How did you work this part out? How did you work out that this was that second of the day? I'm guessing that the 86400 is the total number of seconds for the day? Also, I noticed that on the first comment you made that you had 1/17280. What is the 17280 related to? Is that the total number of seconds of the day?
86400 seconds is the total numbers of seconds of a day. 60 seconds times 60 minutes times 24 hours is 86400 seconds. In the second example the start time is 05:00:36. Start time less 30 seconds is 05:00:06. Five hours and 6 seconds are 18006 seconds. 60 seconds times 60 minutes times 5 hours plus 6 seconds is 18006 seconds and therefore 18006/86400 of one day. Then 30 seconds are added with ROW(A1)/2880 which makes 05:00:36. If the formula is dragged one row down the result is 05:01:06 and so on.
In the first example the start time is 00:00:35. 5 seconds are represented by 5/86400 which is equal to 1/17280. Then 30 seconds are added with ROW(A1)/2880 which makes 00:00:35.
