Averaging mulitple columns/rows if two criteria are met

[livelypilgrim]

I have a dataset consisting of concentrations of parameters (alpha and beta) at different locations over multiple years. I've included an example dataset here.

I need to calculate an average and standard deviation for each parameter that spans multiple locations and years (but not all locations and years).

Example 1: Calculate the average and standard deviation of alpha values from years 2009 to 2012 at locations A and C.

Answer should be: Average of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.53. Standard deviation of {0.84, 0.47, 0.27, 0.14, 0.36, 0.65, 0.66, 0.85} is 0.26.

The real dataset is large, including 7 different parameters and more than 30 locations. I need to perform these calculations for many parameters, so am looking for a formula (or array formula) that will do this in as little cells as possible. Can this be done by formula or will I need a macro?

Location	Parameter	2008	2009	2010	2011	2012	2013
LocA	alpha	0.24	0.84	0.47	0.27	0.14	0.33
LocA	beta	4	9	9	8	2	9
LocB	alpha	0.24	0.33	0.85	0.54	0.56	0.65
LocB	beta	8	7	6	7	2	9
LocC	alpha	0.24	0.36	0.65	0.66	0.85	0.92

[livelypilgrim]

The locations that I need to include and exclude are pre-defined. The table I am trying to generate would look something like this:

Parameter	Average (2009-2012)	Standard Deviation (2009-2012)
alpha (LocA&C)	0.53	0.26
beta (LocA&C)	99	99

Thanks

[icestationzbra]

hi there,

take a look at this and see if this much of automation will work for you...

[livelypilgrim]

Thanks icestationzbra. When I tried to use this on my dataset, I had to modify it to get it to work. I must be doing something wrong in the translation.

The problem seems to lie with the "third criteria" in the 'logical_test' portion of the IF function, specifically this:

(ISNUMBER(MATCH($C$1:$H$1,ROW(INDIRECT(C$11))

To get around my problem, I simply dropped the "third criteria" and modified the 'value-if-true' portion of the IF statement to the specific range I need (2009:2012).

So, for example, in the file you attached, I modified the formula in cell C12 to:

=AVERAGE(IF(ISNUMBER(SEARCH($A$2:$A$6,$B12))*($B$2:$B$6=$A12),$D$2:$G$6))

This is not as hands-off as what you suggested (requires more work if I need to change the range of years of interest). I'm not sure why the function you suggested does not work in my file (I would attach my file but it has some sensitive data in it).

Perhaps my inability to get it to work has to do with the fact that I don't fully understand how the "third criteria" works. I understand everything else in the function you proposed, but this one confuses me. Could you break it down for me? This way I might be able to get it to work for me.

What is this doing: (ISNUMBER(MATCH($C$1:$H$1,ROW(INDIRECT(C$11))

Thanks again for your help.