Hello,

I am working on a spreadsheet with annual salaries in a row. For pension purposes I need to create a formula that will search from DY5:FA5 and will automatically find the three CONSECUTIVE years that total the most. Usually this value is the last three as salaries tend to only rise but is there a way of making sure my data is correct.

## You might also find helpful in Excel...

There are a few ways, but assuming you have Years to head each salary (In row 3)then try this!

In row 4 add a 3 year Rolling sum.. In EA4 enter:

=SUM(DY5:EA5)

THen drag this to the end.

In FB5 add this

=INDEX(DY3:FA3,1,MATCH(LARGE(DY4:FA4,1),DY4:FA4,0))

In FC5 add this

=INDEX(DY3:FA3,1,MATCH(LARGE(DY4:FA4,2),DY4:FA4,0))

In FC6 add this

=INDEX(DY3:FA3,1,MATCH(LARGE(DY4:FA4,3),DY4:FA4,0))

This will return the 3 years of highest consecutive salary, if you have set it up as explained!

Hope that helps!

Good answer.

Appreciate it Andy. OP I hope this helped! Post back with issues!