# 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by