What's better than sum(if()) array formulas??

[EcoKid]

Hi everyone,
New to this board. I am a research biologist and I have an interesting problem. I am collecting location data on an animal species with overlapping detection methods: radio telemetery and trapping. Now, the telemetery locations are daily locations for a number of animals, and the traps are only in the field for a short period of time before they are moved. My question is how many telemetery locations for each animal are within a set distance from the trap durring the time that the trap is opperational.

My data structure:
I have 2 sheets, one for the telemetry locations, one for the traps and analysis.

The telemetry sheet has columns for Animal ID, Date, Easting, Northing (Eastings and Northings are spatial coordinates).

The trap sheet has columngs for Trap ID, Start Date, End Date, Easting, Northing

The analysis part adds columns for each Animal ID to create a large matrix. Thus for each trap ID, you can find out how many times an animal was located within a user defined distance durring the time that the trap was opperational.

Now, I have solved this problem with a sum(if()) array formula, but with the number of telemetery locations (thousands) and traps (hundreds) I have, the spreadsheet takes a good 6 hours to calculate all the cells. So what I want to know is can you think of a way to do this that will be considerably faster?

My formula is something like this:
{=sum(if(Trap!StartDate <= Telemetery!Date(entire column), if(Trap!EndDate >= Telemetery!Date(entire column), if( ((Trap!Easting - Telemetery!Easting(entire column))^2 + (Trap!Northing - Telemtery!Northing(entire column))^2)^.5) <= distance, 1, 0), 0), 0))}

Hopefully that makes some sence to you. The logic is this:

Add 1 for each of the telemetery locations
if(the date of the telemetery locations are greater than the trap start date
and if( the date of the locations are less than the trap end date
and if the telemetery locations are within the set distance of the trap location (calculated with pythagorean)
else add 0

Once again, this works fine, but it is very slow to calculate with large numbers of data points. If anyone knows how to streamline this process, I would be very happy,

Thanks,
EcoKid

 

[wsjackman]

have you concidered a pivot table?

 

[EcoKid]

have you concidered a pivot table?

I have considered it, but I don't know how.

I am new to pivot tables, so maybe there is more functionality than I am aware, but I couldn't find a way to pull data from two separate tables that don't have the same data structure. I am definitely open to this course of action though, as I like how pivot tables work, but someone would have to walk me through how to do it.

 

[pbornemeier]

Please post 30 or so lines of each data set to give us something to play with. See the link to Excel Jeanie in my sig for one easy method to do this.

My first recommendation would be to reduce the data being processed for each trap /time interval:
For a given trap deployment, determine if any traps overlap, if so treat them a one
For each trap or overlapping traps
Determine the map coordinates where the traps could capture an animal.
Filter out any lines of telemetry data that are outside that area, then process only that telemetry data for those traps.
Move to the next trap/time interval

Step 1 of the Pivot Table Wizard (Excel 2003) allows the selection of multiple ranges.

 

[Fazza]

Hi, EcoKid.

With 6 hours to calculate, there must be a lot of data. You should be able to get it calculating quickly with the approach below.

It would have helped if you gave the format of the output. I assume a pivot table would be good. Or otherwise a cross-tab result. As I don't know the format, I've simply created a list (but it would be better I suspect as a pivot table or cross-tab report.)

Create defined name for "traps" (for header & data), "animals" and "distances". (I've assumed there is a table of distances with header "Distance" and values under.) Save the file. Start via menu data, import external data, new database query. Excel files, OK. Browse for your Excel file, OK. See the table names on the LHS - animals, distances, traps. Hit the ">" button and move some fields to the RHS. Hit 'next' a few times and continue until you can select the option to 'view data or edit query in Microsoft Query' then 'finish'. MS Query will now open, hit the 'SQL' button and edit the text to become like below - you should be able to copy & paste it in to fully replace what is initially there.

SELECT DISTINCT D.Distance, T.`Trap ID`, A.Date, A.`Animal ID`
FROM animals A, distances D, traps T
WHERE ((T.Easting-A.Easting)*(T.Easting-A.Easting)+(T.Northing-A.Northing)*(T.Northing-A.Northing)<=D.Distance*D.Distance) AND (T.`Start Date`<=A.Date) AND (T.`End Date`>=A.Date)
ORDER BY D.Distance, A.Date, A.`Animal ID`, T.`Trap ID`

Hit OK to enter this, see the results set on screen in MS Query. Now hit the 'open door' icon and return the results set to the worksheet. (It can take a while to finish this step when it is being created for the first time. I usually overcome this by hitting CTRL-ALT-DELETE a few times to bring up the task manage. Just delete the task manager and repeat. This might only be needed when creating the query: not for future refreshes.) This is now a query table and refreshable like a pivot table. So, right click from within the results table and then 'refresh', or refresh from the external data toolbar or via the data menu.

I guess it will calculate in a second or two, would be interested to hear. (Check it by erasing a few of the worksheet's results cells and then refreshing the query table.)

BTW, there was something a while ago with a similar approach where array formulas took over an hour and this sort of database approach like described above took ~1 second. Link to that thread, http://www.mrexcel.com/forum/showthread.php?t=318970

Regards, Fazza

 

[Fazza]

Hi, EcoKid. Can you advise the status of this question/answer? Regards, Fazza