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
dougf1rAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor 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

Open in new window

0
 
dougf1rAuthor Commented:
File is now embedded
0
 
dougf1rAuthor 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.

All Courses

From novice to tech pro — start learning today.