Forum Discussion
Excel: Help! Formula needed
mathetes Thank you for responding to me.
So for problem A:
With my individuals, there are seals moving in and out over time which is another complexity to my data - I have individuals being added and then removed during the data collection (N=40). But I see what you are saying. I have an idea as to what I'll do to solve that problem using your method. So thank you for that!!
Problem B:
So by time pattern I mean purely the pattern within my data so for example:
| Pool | Date | Time | ID |
| 1 | 16/02/2023 | 03:57:05 | A |
| 1 | 16/02/2023 | 03:57:05 | B |
| 1 | 16/02/2023 | 03:57:05 | C |
| 1 | 16/02/2023 | 03:57:05 | D |
| 1 | 16/02/2023 | 03:57:05 | E |
| 1 | 16/02/2023 | 03:57:35 | A |
| 1 | 16/02/2023 | 03:57:35 | B |
| 1 | 16/02/2023 | 03:57:35 | C |
| 1 | 16/02/2023 | 03:57:35 | D |
| 1 | 16/02/2023 | 03:57:35 | E |
| 1 | 16/02/2023 | 03:58:05 | A |
| 1 | 16/02/2023 | 03:58:05 | B |
| 1 | 16/02/2023 | 03:58:05 | C |
| 1 | 16/02/2023 | 03:58:05 | D |
| 1 | 16/02/2023 | 03:58:05 | E |
| 1 | 16/02/2023 | 03:58:35 | A |
| 1 | 16/02/2023 | 03:58:35 | B |
| 1 | 16/02/2023 | 03:58:35 | C |
| 1 | 16/02/2023 | 03:58:35 | D |
| 1 | 16/02/2023 | 03:58:35 | E |
| 1 | 16/02/2023 | 03:59:05 | A |
| 1 | 16/02/2023 | 03:59:05 | B |
| 1 | 16/02/2023 | 03:59:05 | C |
| 1 | 16/02/2023 | 03:59:05 | D |
| 1 | 16/02/2023 | 03:59:05 | E |
I'm using video time-lapsed data which is taking a snapshot every 30s from midnight to midnight (throughout the day). So as you can see in the section of table above, when there is 5 individuals (for example), there will be 5 copies of the same time which then going into the next "bunch" of cells will be 30s later and so forth. If I select all of the above in time to go down, it only repeats what is above it, which doesn't quite work as I have differing start and stop times (caused by issues with cameras).
I don't think this a "pattern" that excel will recognise or duplicate. Hence why I'm doing it manually.
I've attached the link (hopefully) so you should be able to see the document. There is no data inputted into it as of yet. If it doesn't work, let me know.
https://liveplymouthac-my.sharepoint.com/:x:/g/personal/samantha_mcgivern_students_plymouth_ac_uk/EcvnYEC035NNsmlSELcJYUIBltEvqGysllLC8uKSev-vbg?e=jdfBDW
I also have the latest Microsoft Excel and I'm on a Ventura 13.1
The rightmost 2 columns of your grid could be generate by formula provided they are completely regular.
= LET(
sequence, QUOTIENT(SEQUENCE(rows,1,0,1),sealCount),
time, startTime + sequence*interval,
individual, MOD(SEQUENCE(rows,1,0,1),sealCount),
HSTACK(time, INDEX({"A","B","C","D","E"}, 1+individual))
)