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 data collection and have come across two problems.
I have the following spreadsheet:
Problem A:
The ID (identity) relates to which individual it is. At present they are named A-F. This is so that when they are identified, the ID that the individual is can be added in, as it may be the case that later in the videos the individual is identified. Is there a way for excel to change all related cells to the new number? For example, when the first "a" changes to "105", I want it to change all the "A's" in the rest of the data set to "105" for that date? There will be multiple different days and times featured on the data collection sheet. So if a formula is created that works for this, will it then change it for the rest even over different dates?
Problem B:
So far I haven't seen a solution for this yet. Is there a way for Excel to recognise the time patterns? I have tried the control+E method and Excel doesn't recognise the pattern, so I'm having to manually type each time in (which is every 30s over considerable hours). I have days of footage to do this with but limited time so wondering if there was a faster method of doing this? If not, well, long days it is!
Any help regarding this would be really appreciated!
16 Replies
I'm working with seals so it is difficult to identify individuals. I'm trying to create a spreadsheet ready for data collection and have come across two problems.
So at least there are no concerns with violating their privacy here. 🙂 It might be helpful, since that worry doesn't apply, if you could post the actual spreadsheet rather than just an image. Use OneDrive or GoogleDrive and paste a link here that grants access.
That said, let's look at each of your questions:
Problem A: The ID (identity) relates to which individual it is. At present they are named A-F. This is so that when they are identified, the ID that the individual is can be added in, as it may be the case that later in the videos the individual is identified. Is there a way for excel to change all related cells to the new number? For example, when the first "a" changes to "105", I want it to change all the "A's" in the rest of the data set to "105" for that date? There will be multiple different days and times featured on the data collection sheet. So if a formula is created that works for this, will it then change it for the rest even over different dates?
Answer A: yes. That should be fairly easy.
- To be on the safe side, I'd suggest adding a "helper column" to the right of the current ID column, header (perhaps) "Name" or some other heading that indicates "we've actually identified this individual."
- "safer" in that doing it this way doesn't destroy the original ID, just in case there's "re-identification" later in the process. I believe in keeping history, in that sense.
- Then create a table on a separate sheet in the same workbook where you can define the relationship between the temporary letters (your current ID) and the more verified "Name." (I've attached a sample workbook illustrating this, but as noted, if you could post your real one, then we can be fully on the same page.)
- Then in the helper column, use a formula like this (see attached spreadsheet for how it works). This uses the LET function, so you'd need a new version of Excel or a subscription to Microsoft 365. I'm on a Mac myself, so that per se is not an issue.
=LET(known,INDEX(Table2[Name],MATCH([@ID],Table2[ID],0)),IF(known=0,"",known))Problem B: So far I haven't seen a solution for this yet. Is there a way for Excel to recognise the time patterns? I have tried the control+E method and Excel doesn't recognise the pattern
Answer for the moment: What do you mean by time pattern in this case. What kind of pattern? What is the nature of the pattern you're wanting to recognize? Frequency? Intervals? ??? Duration? etc.
Finally: what version of Excel do you have? And, while we're at it, what Mac OS?
Just going back to problem A, I've realised I've not explained in the best way. So the ID are unknown individuals at that time and then when they are identified I would like to be able to change within the column to what the individual actually is. And then any individual that is not ID-ed could then be left as unknown C for example. But I only want this formula to work for one date at a time if possible as the individuals can change from data to date and from pool to pool.
So the ID are unknown individuals at that time and then when they are identified I would like to be able to change within the column to what the individual actually is. And then any individual that is not ID-ed could then be left as unknown C for example. But I only want this formula to work for one date at a time if possible as the individuals can change from data to date and from pool to pool.
I understand leaving unknown individuals as unknown. What I don't understand is the desire for even those who ARE known, who have been identified, to be only known "one day at a time." Why is that? Why can't individual A, once identified--for sake of argument, as "SealJohn Doe"--show up on different days and different pools? That is to say, don't individuals, once identified, retain that known identity? Wouldn't you want to track them accordingly, across multiple days, multiple pools?
This also goes back to my first suggestion, that you retain the initial IDs as part of history, but also associate them with a known name or label, once they have been identified, in a distinct column.
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)) )
- To be on the safe side, I'd suggest adding a "helper column" to the right of the current ID column, header (perhaps) "Name" or some other heading that indicates "we've actually identified this individual."
