Multi function look up/if/match

Hi All,

I have attached a very abridged version of the spreadsheet I am working on, hopefully it works as I have never attached any thing here before.

Background. I have been undertaking motion sensing camera monitoring of wildlife for the last 5 years. I have over 200,000 records of species. I have split this down into each species, some of which have well over 10,000 records alone. An example of the data captured is in the Fox Raw Tab. This includes data about when the camera was put out, when it came in, when the record was taken, what species it was, and the site name.

I am working with an ecologist who wants to undertake an 'occupancy' analysis of this data. This involves providing data about when species are seen in the form of zeros and ones against the date, zero is no record that day and one is any number of records that day.

The format they also want the data in is day 1 being the start of the entire program (1/7/2011) and day 2192 as the end (30/6/2017).

The issue I face is that the each site (there are over 400) may have only had a camera out for a month, some two months, some the whole time. The ecologist only wants a zero or one in the cells that correspond to the time the camera was at that site and active, any other time should return NA. Without this their analysis will not be at all accurate.

In the attached file there are various tabs, what the tabs are. Fax Raw is the raw data as briefly described above. Fox Admin is where I have done a countifs which gives me the count of records against the date and time +12 hour column in fox raw (the ecologist wants days to be from midday to midday, not mid night to midnight, hence the plus 12 hours). This works and I can get to zeros and ones easily, what I can't do is get the NA to come up. The Fox Occ tab has the data in zeros and ones. and Sites Admin is irrelevant to this but needed to maintain the raw site data correctly.

What I need to be able to do. Enter a formula into foxOcc C4 (or a whole new tab set up the same way), that will search for a match of the site code in FoxOcc A and the site code in FoxRaw column L, and then sort to see if the date in FoxOcc row2 is between the dates in FoxRaw Columns C&E. If it isn't between these dates then return a NA, and if it is between these dates then return the corresponding cell value from Fox Admin as a one or zero (fox admin is a count so has higher numbers than 1.

I hope all this makes sense and I didn't provide too much background, I included it thinking the context might help.

Thanks in advance.

Chris

Hi Glenn,

Sorry for all the words. I have manually entered what I hope to see into the Fox Admin tab, if you scroll right to late 2015 you will see the NA, zeros, and ones. I have left the other formulas in.

In essence I need to match the day the photo was taken and the site code and return a value. I got that done in the formula in there. What I can't work out is how to add the condition about the time the camera was in the field (date deployed and date retrieved) so it will return an NA value if the camera wasn't out in the field at the time.

Thanks

Hi AVK,

For the occupancy analysis to work I need the specific dates records were found and the dates the camera was out but nothing was found. Occupancy is basically the statistical chance of something being in an area so you need the number of times they were, what days they were, and what days they weren't. My problem is that if I include the times the cameras were not out as zeros (zero being when nothing is observed but a camera is out) then it will throw out the statistics massively and mean the results are useless.

Thanks for the suggestion.
Chris

Thanks for your help. This is solved. The Array formulae worked a treat, although it did take about 8 hours for my computer to compute the whole lot.