look up / match / reference cell

[Guest]

Hi
Am wanting to determine a 3 month average based on the 3 months proceeding
the current month.

The spreadsheet contains 12 months of data. If the current month is
determined elsewhere in the data on the spreadsheet, how can I use that so
that I can determine the 3 month average for the months preceeding the
current month.
Eg

Current month = May

jan feb mar apr may june july aug sep oct nov dec last 3
month avg
5 7 10 6 9
??

(based on feb mar apr)

When June comes around the 3 month average should then move to be based on
Mar, Apr & May.

Any thoughts??

 

[T. Valko]

What do you do in Jan, Feb and Mar when there aren't 3 preceding months?

 

[Roger Govier]

Hi Kylie

Assuming your data is in A1:N1
enter in A2
=IF(COUNT(OFFSET(A$1,0,-2,1,3))=3,AVERAGE(OFFSET(A$1,0,-2,1,3)),"N/A")

and copy across

 

[Guest]

Good point! It's a new reporting structure I am implementing so obviously
for the first 3 months the average won't be applicable.

 

[T. Valko]

OK, if you want an average for each 3 month period:

Assuming:

A1:L1 = month names
A2:L2 = numeric values

Enter this formula in D3 and copy across to L3:

=IF(D2="","",AVERAGE(B22))

If you want just a single rolling average:

=IF(COUNT(A2:L2)<4,"insufficient
data",AVERAGE(OFFSET(A2,,COUNT(A2:L2)-1,,-3)))

This assumes each month data will be entered from left to right and there
will not be any months where no data is entered.

 

[Guest]

Hi Roger

Thanks for your repsonse. Sorry I don't think I was clear in my question in
that I want to be able to calculate the 3 month average based on the
condition of the current month. Bascially, the user would open the
spreadsheet, select the current month from a drop down sheet, input the data
for that month, and then the 3 month average (for the preceeding 3 months)
would be calculated and returned in the last column. I'm not much of an
excel guru but don't think your formula takes into account the cell that
references what is the current month.

Perhaps I am being overly enthusiastic with what I want to do!

Thanks again
Kylie

 

[ilia]

How about this?

Create a cell with a validation rule type List, to contain the row
headers. In my example, the row headers are in B3 through M3,
corresponding to January through December. The validation rule is in
cell E1. The values for each month are in B4 through M4. The
following formula will correctly work for any month after February.

=AVERAGE(OFFSET($B$4,0,MATCH($E$1,$B$3:$M$3,0)-2-COLUMN($B$4)+1,1,3))

This format allows you to have first data value ($B$4 in my example)
in any column, if you ensure that the same cell is referenced in the
COLUMN function. Best bet is probably to have several named ranges
for the references above, including B4. Let's modify this formula,
where a named range FirstValue refers to $B$4, LastMonth refers to $E
$1, and MonthNames refers to $B$3:$M$3:

=AVERAGE(OFFSET(FirstValue,0,MATCH(LastMonth,MonthNames,0)-2-
COLUMN(FirstValue)+1,1,3))

Don't forget to change your data validation rule for $E$1 to
MonthNames. Also notice some hard-coded variables. Let's say you may
want a different number than 3 months. In that case, let's say my
value of "Last n months" is in G1. Here's the new formula:

=AVERAGE(OFFSET(FirstValue,0,MATCH(LastMonth,MonthNames,0)-($G$1-1)-
COLUMN(FirstValue)+1,1,$G$1))

Naming a range NumMonths to refer to $G$1, we get the following:

=AVERAGE(OFFSET(FirstValue,0,MATCH(LastMonth,MonthNames,0)-
(NumMonths-1)-COLUMN(FirstValue)+1,1,NumMonths))

Now this formula will return the desired result for any month after
G1th month of the year. By creating larger ranges (to span several
years) and using the same defined names, you can produce a variety of
results with the same formula. Getting more creative, you could
compare current year's average to the same three months a year ago,
etc.