Calculate salary average

I have a list of people with 10 years of salary history for each (in ten consecutive columns on the spreadsheet). I need to calculate salary average for 5 consecutive highest years within the last 10 years. Please see example. If employee has less than 5 years, then it should just average the years employee has. Some people have breaks in service, for example employee worked 2011, 2010, 2009 and 2005, 2004, 2003. 2008, 2007, 2006 years should be ignored and skipped in calculating the averages to the next. Depending which combination (2011, 2010, 2009, 2005, 2004 or 2010, 2009, 2005, 2004, 2003) has the highest average would be consider.

Could someone please help me? Example 1.xls

Thank you.

P.S. I worked on this project over a month ago, and daddylonglegs provided the formula:
=IF(COUNT(B2:K2)<5,AVERAGE(B2:K2),MAX(SUBTOTAL(9,OFFSET(B2,0,COLUMN(B2:G2)-COLUMN(B2),1,5))/5))
However after reviewing and testing over 3000 people, it turned out the formula does not work properly.

Have a look at a pivot table, it may solve the issue for you also.

Try this array formula.

Please Login or Register  to view this content.

Hi Coreytroy, I am not that advanced and I have never used pivot table.

Hi DGagnon, I need to use excel format for this project. Thanks.

What I need is 5 highest consecutive years. If there are break between years, I need to skip the breaks, like they would not exist and still calculate the highest consecutive years. If there are less than 5 years, the average is calculated based on the years available. I always have to consider all 10 years. because it is possible that 2002-2006 are the highest.

Thank you dadylonglegs. It's possible I didn't explain well the requirements the first time. It seems there is a glich in your new formulaExample - New Formulas.xls. The formula doesn't calculate properly when the highest 5 are salaries 6-10. Please see attached. Your first formula worked well, except it counted empty cells as 0 into average.

Thank you so much. I think it does work well for all scenarios. I greatly appreciate your help.