Need to sum 3 adjacent values in a row that have the highest value.

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.

3 thoughts on “Need to sum 3 adjacent values in a row that have the highest value.

  1. Will

    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!

Leave a Reply

Your email address will not be published. Required fields are marked *