Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating MAX, MIN & AVG for each year
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating quarters of a year. | Excel Worksheet Functions | |||
Calculating year to date... | Excel Discussion (Misc queries) | |||
Calculating Dates Within Fiscal Year | Excel Discussion (Misc queries) | |||
Calculating by individual months of the year | Excel Discussion (Misc queries) | |||
Calculating number of Saturday's in a Year | Excel Discussion (Misc queries) |