AVERAGEIF Formula - Help I must be doing something wrong, but cannot figure out what...!

I have separate years and need to average the values contained in the tables, but only if there is a value greater than "0". I have looked at the other posts regarding this and thought I was following the examples, but I keeps getting an error message. I need to find the average for the values across all of the years, excluding anything that is not greater than "0."

The data is found in the following cells:

2005 has values found in F16:F23 (An average for this year is in F24)
2006 has values found in F27:F34 (An average for this year is in F35)
2007 has values found in F38:F45 (An average for this year is in F46)
2006 has values found in F49:F56 (An average for this year is in F57)
2008 has values found in F60:F67 (An average for this year is in F68)
2009 has values found in F71:F77 (An average for this year is in F78)
2010 has values found in F81:F84 (An average for this year is in F85)

I have tried using the formula wizard with the "AVERAGEIFS" formula, but filling in the values as it states still gives me an error even without trying to add an IFERROR( ) surrounding the equation in case there are no values greater than 0. Any suggestions? I appreciate your help and patience.

=averageifs(F16:23,F16:F23,"<>"&0,F16:F23,"="&2005) in F24

Try this

Punnam

Originally Posted by Punnam

=averageifs(F16:23,F16:F23,"<>"&0,F16:F23,"="&2005) in F24

Try this

Punnam

Thank you for your quick response. but in F24 there is an average for the year 2005 already recorded. Each of the years (2005 - 2010) are in separate tables and an average has been determined for each year. Now I need to go back and determine what the average for all of the values for 2005 through 2010 will be, excluding the values that are not greater than "0." The averages for each year are found in F24, F35, F46, F57, F68, F78, and F85 for 2005 through 2010 respectively. Either the average values as stated above can be used in determining the overall average for the combined years or the values found in the ranges of F16:F23, F27:F34, F38:F45, F49:F56, F60:F67, F71:F77, and F81:F84 (Note this option excludes the cells F24, F35, F46, F57, F68, F78, and F85). The equation you have given does not appear to address the issue I am having unless I have missed something.

Hi a sample workbook without any confidential data will be more help full

Punnam

Originally Posted by Punnam

Hi a sample workbook without any confidential data will be more help full

Punnam

Here is a sample worksheet like the one I am working with...

Sample Worksheet.xlsx