Calculating MAX, MIN & AVG for each year
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
|