I am hoping to simplify the following formula that works as intended:
where H4 houses the number representing the year. The formula then finds the maximum value for the selected year and the rows that have the previous two years. So for example if H4 = 2013, then the formula seeks the maximum value in 2013, 2012 and 2011.
I tried =MAX(INDEX((YEAR($B$7:$B$40)=$H$4:$H$4-2)*$D$7:$D$40,0)), but that only got the maximum in the year 2011.
Thanks.
Excel 2003
Code:
=MAX(INDEX((YEAR($B$7:$B$40)=$H$4)*$D$7:$D$40,0),INDEX((YEAR($B$7:$B$40)=$H$4-1)*$D$7:$D$40,0),(INDEX((YEAR($B$7:$B$40)=$H$4-2)*$D$7:$D$40,0)))
where H4 houses the number representing the year. The formula then finds the maximum value for the selected year and the rows that have the previous two years. So for example if H4 = 2013, then the formula seeks the maximum value in 2013, 2012 and 2011.
I tried =MAX(INDEX((YEAR($B$7:$B$40)=$H$4:$H$4-2)*$D$7:$D$40,0)), but that only got the maximum in the year 2011.
Thanks.
Excel 2003