+ Reply to Thread
Results 1 to 10 of 10

Multi function look up/if/match

  1. #1
    Registered User
    Join Date
    12-20-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    9

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multi function look up/if/match

    Yes. Too many words. can you manually enter some expected results onto your sheet and remove the formulae that don't work?
    Glenn



  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,890

    Re: Multi function look up/if/match

    Dear Chris, I am very happy to you are working for wildlife sector. I am also wildlife lover. We regularly visited different different "wildlife sanctuary" specially "tiger reserve". Now come to the point.
    While going through your data record & post. I think, You are trying to find out how many Days camera deployed & how many total count for particular side code.
    Is it correct. If yes then why not using "Pivot Table" instead of mentioned large number of days & complicated criteria.
    Please refer attach file in sheet "Pivot"
    Attached Files Attached Files


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  4. #4
    Registered User
    Join Date
    12-20-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    9

    Re: Multi function look up/if/match

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-20-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    9

    Re: Multi function look up/if/match

    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

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multi function look up/if/match

    Can a camera be out to the same site for more than one period? If so, you may need to introduce some sort of unique identifier...

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multi function look up/if/match

    here's one way, using an array formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

    Is there any reason why you have the dates going along a row instead of down a column? It "feels" very clunky and awkward...
    Attached Files Attached Files

  8. #8
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,890

    Re: Multi function look up/if/match

    Dear Chris, Plz look attach revised file. In this file in Pivot, i more add column (such as Photo, Total Days, Date Deployed)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-20-2016
    Location
    Melbourne, Australia
    MS-Off Ver
    Windows 10
    Posts
    9

    Re: Multi function look up/if/match

    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.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Multi function look up/if/match

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multi Tier - Match & Vlookup or INDEX or INDIRECT function help needed
    By yogananda.muthaiah in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2016, 12:32 AM
  2. [SOLVED] Multi-Function Equation Using LOOKUP, VLOOKUP, MATCH, INDEX?
    By EverClever in forum Excel General
    Replies: 16
    Last Post: 01-26-2015, 04:49 PM
  3. New & Lost: Multi Criteria & Multi Row INDEX, SMALL, MATCH...
    By morleyp in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-23-2014, 12:40 PM
  4. Match Function for multi-numbers
    By AlwaysMe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-05-2013, 02:28 AM
  5. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  6. Multi level match function
    By drean03 in forum Excel General
    Replies: 4
    Last Post: 11-19-2009, 07:55 AM
  7. Excel 2007 : Multi level Match function
    By drean03 in forum Excel General
    Replies: 1
    Last Post: 11-19-2009, 07:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1