# Average columns of data based on values in another column

original_question.xlsx

A solution to a similar question to this one has previously been provided:
https://www.experts-exchange.com/questions/29094062/Lookup-data-based-on-values-in-another-column.html

This time, what is needed is the average value of the 30 years of "n" data leading up to and including the year listed in the "year" field (Column B). This result should be included in the "n_30yr_avg" field (Column C).

The first 52 values for "n_30yr_avg" have been solved manually in the attached worksheet.

Is there a solution that would provide this calculation for all records?
LVL 1
###### Who is Participating?

Commented:
You can use this formula
``````=SUMPRODUCT((RIGHT(\$D\$1:\$BJ\$1,4)*1>B2-30)*(RIGHT(\$D\$1:\$BJ\$1,4)*1<=B2)*D2:BJ2)/30
``````
0

Author Commented:
File is now embedded
0

Author Commented:
Works great, many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.