formula for calculating averages of other cells based on multiple criteria

Hi,

I'm a graduate student in Ecology, and I have a large data set with a year column, a day-of-year (as in Jan 1 = 1, Dec 31 = 365), a trap site column (e.g. trap 2a, 4g, etc. up to 7h), and a value ("Pr") for the number of spiders caught in each trap divided by the number of days that the traps were out (a measure of the total caught standardized by trapping effort). What I need to figure out is how to write a logical function to create a new column that will, for each trap, provide the average and standard deviation of the OTHER traps collected that day. For example: if there are three traps, 2a, 2b, and 2c, collected 1999, on day 211, in the 2a row would be the average value and SD of 2b and 2c, in the 2b row, of 2a and 2c, and in the 2c row, of 2a and 2b. So the function has to pick out same year and day, but different trap site, then average them. Any ideas? I'm attaching a very abbreviated and simplified version of my sheet as well (with the new values in two separate columns that I would want to be able to calculate for a 5000+ row sheet), in case that helps. Thanks! Also, I have excel 2008 for Mac 12.3.1

Best,
Mike

In G2 and copy down,

=AVERAGEIFS($D$2:$D$13, $A$2:$A$13, $A2, $B$2:$B$13, $B2, $C$2:$C$13, "<>" & $C2)

In H2, this formula MUST be confirmed with Ctrl+Shift+Enter:

=STDEV(IF(($A$2:$A$13=$A2) * ($B$2:$B$13=$B2) * ($C$2:$C$13<>$C2), $D$2:$D$13))

Copy down.