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...
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.
It may help if I explain the context a bit.
So, there are 4 separate pools within this rehabilitation site and each of the 4 pools has been recorded on the same day. Each individual within the pool changes from recording to recording (Day to day), sometimes due to the individual either being released or moved to another pool as part of the rehab programme. Some of the individuals do not move from one pool to another. For example pool 1 is a release pool so some individuals are not seen in other pools. On the same day of recordings there can be for example 5 individuals in pool 1, 4 individuals in pool 2, 6 individuals in pool 3 and 12 individuals in pool 4. All of these seals are different and are only the same individuals in a handful of cases.
The A, B, C, D are purely just notary of all individuals in the pool at that time until I can successfully ID that individual through focal following (following that individual).
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?
Yes, they do retain their identity, but because of the video footage, it can be incredibly difficult to ID each individual, or even the same individual, across multiple clips. So each of the videos (day) has to be taken as if it was a new piece of footage as for example the individual you were able to identify in the 16/2/2023, you may not be able to identify for certain in the clip from the 20/2/2023 for a number of reasons. The identification process has to be started anew every time you go into a new data clip for an alternate day for this reason. Seals, especially grey seals, can be difficult to identify as I have experienced, for example, 3 seals in one pool all look alike but the only identifiable difference is their flippers which you are unable to see due to these being underwater.
Additionally the recording days are not consecutive. Due to technical problems, the cameras were only able to record at least every 2 days, which after the 16/2/2023 was increased to 4 days due to the battery life of the cameras failing (the cameras are solar powered and this is the first project to use these cameras).
Additionally these individuals already have an identity that is corresponded to further data - such as weight. So matching the behavioural data - what this data collection sheet is for - to that individual has to match exactly.
So, in theory, by using the method suggested, I would have to create multiple tables because of how the footage has turned out. I simply want to be able to change that letter (e.g. A) for that day for the correct identity (e.g. 105), and then for that date have it change all "A" to the correct ID "105".
It may help if I explain the context a bit.
It always helps to explain the context. Always. It helps eliminate (by anticipating) questions of defining the process.
I don't know if this would meet your need as I now understand it. I've changed the reference table to allow for different IDs to Name connections based on date. And the new formula takes account of both ID and Date in that two dimensional matrix.
=INDEX(Tables!$B$2:$D$7,MATCH([@ID],Tables!$A$2:$A$7,0),MATCH([@Date],Tables!$B$1:$D$1,0))Would this work for multiple pools? So, as mentioned before, the ID would potentially be different for each pool as well as each day. So it is more of a 3 dimensional mix than a 2 dimensional mix. Would this mean that I would have to create a table for each pool with the dates across the top and ID as the first column or would there be another way to do this?
Thank you for all of your help. It is really appreciated.
Would this work for multiple pools? So, as mentioned before, the ID would potentially be different for each pool as well as each day. So it is more of a 3 dimensional mix than a 2 dimensional mix.
There are ways to do it. Unfortunately, I'm going to have to pass for now...driving to the airport to go on a trip very shortly and still need to pack.
I had in mind that you would have a separate sheet for each run of observations since any patterns from one set of recordings do not simply read over to the next. I hard-wired the letter IDs
= {"A","B","C","D","E"}into the formula but, if it were instead a reference to a range on the worksheet, then simply overwriting a letter by the ID would change every reference to the specific seal within the relevant pool.
