Hi All, I have a spreadsheet that has historical data but I just want the
most recent 12 month average (months are across the top, metric names are in
Column A)... So Units Sold is in A2, and B1 has Jan 2000 C1 Feb 2000 and so
on. In B2, C2, D2 and so on sales data for the respective month is added. I
have another sheet that has the 3 most recent months in 3 columns then
another column that has a benchmark as a 12 month average of the most recent
12 months.
My question is, how can I automate the average so it will grab the most
recent 12 months...?

Assuming source data in Sheet1, where B1:IV1 contains the "1st-of-month" real
dates formatted to display as "mmm yyyy", with B1: Jan 2000, C1: Feb 2000,
.... as posted

In Sheet2,

Put in say, E2, and array-enter the formula by pressing CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=AVERAGE(OFFSET(Sheet1!A2,,MATCH(TEXT(TODAY(),"mmm yy"),TEXT(Sheet1!$B$1:$IV$1,"mmmyy"),0),,-12))
Copy E2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KCi" wrote:
Hi All, I have a spreadsheet that has historical data but I just want the
most recent 12 month average (months are across the top, metric names are in
Column A)... So Units Sold is in A2, and B1 has Jan 2000 C1 Feb 2000 and so
on. In B2, C2, D2 and so on sales data for the respective month is added. I
have another sheet that has the 3 most recent months in 3 columns then
another column that has a benchmark as a 12 month average of the most recent
12 months.
My question is, how can I automate the average so it will grab the most
recent 12 months...?

I'm sorry, after reading my question I think I may not have been clear.
The data that I'm trying to average is not dates, it would be sales data per
se, so Jan 2000 would have 534 then Feb 2000 would have 560 and so on... I
guess the months don't necessarily matter. I am just trying to average the 12
most recent values in row 11 (or row 12 or 4 or 6 for any metric I have).
So, I guess as the data (not necessarily the months) progress, I want to
average the 12 most recent values for a specific row.
Does that better illustrate what I am trying to explain?

Thanks!!!

"Max" wrote:

Assuming source data in Sheet1, where B1:IV1 contains the "1st-of-month" real
dates formatted to display as "mmm yyyy", with B1: Jan 2000, C1: Feb 2000,
... as posted

In Sheet2,

Put in say, E2, and array-enter the formula by pressing CTRL+SHIFT+ENTER
instead of just pressing ENTER:
=AVERAGE(OFFSET(Sheet1!A2,,MATCH(TEXT(TODAY(),"mmm yy"),TEXT(Sheet1!$B$1:$IV$1,"mmmyy"),0),,-12))
Copy E2 down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KCi" wrote:
Hi All, I have a spreadsheet that has historical data but I just want the
most recent 12 month average (months are across the top, metric names are in
Column A)... So Units Sold is in A2, and B1 has Jan 2000 C1 Feb 2000 and so
on. In B2, C2, D2 and so on sales data for the respective month is added. I
have another sheet that has the 3 most recent months in 3 columns then
another column that has a benchmark as a 12 month average of the most recent
12 months.
My question is, how can I automate the average so it will grab the most
recent 12 months...?

Think I got your intent right the first time. We're just using the date col
headers in Sheet1's B1:IV1 to pin-point the correct col for the average to
cover

Just change the cell ref "A2" in this part: .. OFFSET(Sheet1!A2,,
to whatever row that you want the average for. Eg if you want to average for
row11, just use, array-entered:

=AVERAGE(OFFSET(Sheet1!A11,,MATCH(TEXT(TODAY(),"mm myy"),TEXT(Sheet1!$B$1:$IV$1,"mmmyy"),0),,-12))

(I had presumed you wanted the averaging to start for row2 down in Sheet1,
since you mentioned sales data in B2, C2, D2, etc .. )

Test it out and see that it returns the correct results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KCi" wrote:
I'm sorry, after reading my question I think I may not have been clear.
The data that I'm trying to average is not dates, it would be sales data per
se, so Jan 2000 would have 534 then Feb 2000 would have 560 and so on... I
guess the months don't necessarily matter. I am just trying to average the 12
most recent values in row 11 (or row 12 or 4 or 6 for any metric I have).
So, I guess as the data (not necessarily the months) progress, I want to
average the 12 most recent values for a specific row.
Does that better illustrate what I am trying to explain?

Thanks!!!

Is there a way to create the formula so it does not depend on whether or not
the month header in row 1 is filled in? So, it would be strictly based on the
last 12 non-null values in a specific row?

Thanks again for your help!!!


"Max" wrote:

Think I got your intent right the first time. We're just using the date col
headers in Sheet1's B1:IV1 to pin-point the correct col for the average to
cover

Just change the cell ref "A2" in this part: .. OFFSET(Sheet1!A2,,
to whatever row that you want the average for. Eg if you want to average for
row11, just use, array-entered:

=AVERAGE(OFFSET(Sheet1!A11,,MATCH(TEXT(TODAY(),"mm myy"),TEXT(Sheet1!$B$1:$IV$1,"mmmyy"),0),,-12))

(I had presumed you wanted the averaging to start for row2 down in Sheet1,
since you mentioned sales data in B2, C2, D2, etc .. )

Test it out and see that it returns the correct results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KCi" wrote:
I'm sorry, after reading my question I think I may not have been clear.
The data that I'm trying to average is not dates, it would be sales data per
se, so Jan 2000 would have 534 then Feb 2000 would have 560 and so on... I
guess the months don't necessarily matter. I am just trying to average the 12
most recent values in row 11 (or row 12 or 4 or 6 for any metric I have).
So, I guess as the data (not necessarily the months) progress, I want to
average the 12 most recent values for a specific row.
Does that better illustrate what I am trying to explain?

Thanks!!!

"KCi" wrote:
Is there a way to create the formula so it does not depend on whether or not
the month header in row 1 is filled in? So, it would be strictly based on the
last 12 non-null values in a specific row?

That's a different question.

In Sheet2, to average it for data in say, Sheet1's row11,
you could try, array-entered in say, E2:
=AVERAGE(OFFSET(Sheet1!A11,,MATCH(MAX(IF(Sheet1!$B 11:$IV11<"",COLUMN($B11:$IV11))),IF(Sheet1!$B11:$ IV11<"",COLUMN($B11:$IV11))),,-12))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---