Calculating highest Consectuive years out of 10

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks to Frank Kabel who came up with a solution to a tedious process. The solution works well if the spreadhseet is laid out the way I was told it was going to be laid out but of course, it is the opposite and the formula does'nt seem to convert well. Here is the scenario
Employees are listed in Column A. Columns B,C,D could hold other info and to the right of that area are label acrossl the columns for Year 1995,1996,1997,etc thru 2004. Annual Salaries are recorded for those years. We need to sum the highest five consecutive years. That data could be from 1998-2000 or 1995 to 2000, etc. Can you help me come up with a formula to do just that. Thanks

Frank, if you can show me how to get the offset with idirect to convert to this lay out, I would appreciate it.
 
Is it only ever 10 years in those cells or does the range increase - Assuming it
doesn't, and that you have headers in Row 1, so your first data point is in E2
through to N2, then in say P2 put the following and array enter the formula:-

=MAX(SUMIF(OFFSET($E2,,ROW(INDIRECT("1:6"))-1,,5),">0"))

(Adaptation of Frank's formula)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Kay said:
Thanks to Frank Kabel who came up with a solution to a tedious process. The
Click to expand...
solution works well if the spreadhseet is laid out the way I was told it was
going to be laid out but of course, it is the opposite and the formula does'nt
seem to convert well. Here is the scenario:
Employees are listed in Column A. Columns B,C,D could hold other info and to
Click to expand...
the right of that area are label acrossl the columns for Year 1995,1996,1997,etc
thru 2004. Annual Salaries are recorded for those years. We need to sum the
highest five consecutive years. That data could be from 1998-2000 or 1995 to
2000, etc. Can you help me come up with a formula to do just that. Thanks!
Frank, if you can show me how to get the offset with idirect to convert to
Click to expand...
this lay out, I would appreciate it.
 
Back
Top