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.
I also have the latest Microsoft Excel and I'm on a Ventura 13.1
by time pattern I mean purely the pattern within my data
Of course it's a pattern within the data.
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 apologize if I'm being dense, but although I get that there are five instances of the same time, and then another bunch, "and so forth," it's still not quite clear how you're defining what a pattern IS. (And maybe more to the point, what it ISN'T, which is a contrast question that sometimes makes definitions clearer). Where (when?) does a pattern begin and end? What makes it a "pattern" in your definition? Why would some combinations NOT be a pattern?
If we can get a clear definition (clarity being achieved by defining boundaries) of what constitutes a pattern, it is entirely possible that we could create a formula/function that will recognize same.
I was able to open your file on OneDrive. Thank you.
FWIW, my Ventura is 13.3.1 ... a slight update. I'm glad you have the new Excel versions, since that gives us access to some neat new features which might help identifying/recognizing patterns.