Calculating MAX, MIN & AVG for each year

I have a long list of data, from which I need to calculate the Maximum,
Minimum and Average values for each seperate year. The data spans many
years. An extract is provided below:

Row Column A Column B
1 "full_date" "full_con"
2 17/12/07 13.3
3 21/12/07 18.3
4 23/12/07 8.1
5 24/12/07 8.1
6 7/01/08 12.4
7 20/01/08 11.6
8 27/01/08 12.3
9 8/02/08 14.0

The data is sorted in date order and contained in named ranges as indicated.

Any suggestions and/or assistance will be greatly appreciated. I've looked
at the MATCH & INDEX functions, but couldn't make sence of them in this
context.

--
Brisbane, Australia

Hi,

Try these

=AVERAGE(IF(YEAR($A$2:$A$9)=2007,$B$2:$B$9,FALSE))
=MIN(IF(YEAR($A$2:$A$9)=2007,$B$2:$B$9,FALSE))
=MAX(IF(YEAR($A$2:$A$9)=2007,$B$2:$B$9,FALSE))

All of these are array formula and must be commited using CTRL+Shift+Enter
and not just enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself.

Mike
"dc059" wrote:

I have a long list of data, from which I need to calculate the Maximum,
Minimum and Average values for each seperate year. The data spans many
years. An extract is provided below:

Row Column A Column B
1 "full_date" "full_con"
2 17/12/07 13.3
3 21/12/07 18.3
4 23/12/07 8.1
5 24/12/07 8.1
6 7/01/08 12.4
7 20/01/08 11.6
8 27/01/08 12.3
9 8/02/08 14.0

The data is sorted in date order and contained in named ranges as indicated.

Any suggestions and/or assistance will be greatly appreciated. I've looked
at the MATCH & INDEX functions, but couldn't make sence of them in this
context.

--
Brisbane, Australia

Mike,

Many thanks - I've got the MAX & MIN formulas working, but the AVERAGE
doesn't. I am using the array command (CTRL+SHIFT+Enter), but it only places
a curly bracket at the start of the equation not the also at the end - I end
up with a result of "TRUE" or "FALSE"; not a value. "TRUE" is returned when
I use a small sample set of data; "FALSE" when I reference the total data set
(using the named range)which contains numerous "null" or zero values. Should
I be using the AVERAGEIF function? If so, any suggestions on using it in
this context would again be appreciated.

DC
--
Brisbane, Australia


"Mike H" wrote:

Hi,

Try these

=AVERAGE(IF(YEAR($A$2:$A$9)=2007,$B$2:$B$9,FALSE))
=MIN(IF(YEAR($A$2:$A$9)=2007,$B$2:$B$9,FALSE))
=MAX(IF(YEAR($A$2:$A$9)=2007,$B$2:$B$9,FALSE))

All of these are array formula and must be commited using CTRL+Shift+Enter
and not just enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself.

Mike
"dc059" wrote:

I have a long list of data, from which I need to calculate the Maximum,
Minimum and Average values for each seperate year. The data spans many
years. An extract is provided below:

Row Column A Column B
1 "full_date" "full_con"
2 17/12/07 13.3
3 21/12/07 18.3
4 23/12/07 8.1
5 24/12/07 8.1
6 7/01/08 12.4
7 20/01/08 11.6
8 27/01/08 12.3
9 8/02/08 14.0

The data is sorted in date order and contained in named ranges as indicated.

Any suggestions and/or assistance will be greatly appreciated. I've looked
at the MATCH & INDEX functions, but couldn't make sence of them in this
context.

--
Brisbane, Australia