Monthly Average Previous 12 Months

[rjm65]

I need to calculate a monthly average based on the previous 12 month
amounts of quantities received.

My data is entered in 3 columns, Column A has the date, Column B ha
the Quantity Received, and Column C has the Average.

Every time a shipment arrives the data is entered in it's own row
There are some months when we may receive multiple shipments, and othe
months when no shipments will arrive. I currently derive the average b
subtracting the newest entry in Column A (the date of the lates
shipment) from the firt entry made in Column A, divide by 30 and tha
gives me total months, which I can then divide the sum of column B b
for my monthly aveage.

This works for now because we only have a couple months worth of data
but eventually would like for it to only use the previous 12 month
data for working out the average.

Given the way the data is being entered, is there someway that I ca
have Column C only "look back" 12 months for calculating the average?

Thanks in advance,
Raymon

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER)
=SUMIF(A1:A100,">=" &
DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)/12

 

[Myrna Larson]

Assuming that today, July 15, you want to sum the data beginning with July 1
2003 and including anything in July, 2004:

=SUMIF(A2:A100,">"&DATE(YEAR(TODAY())-1),MONTH(TODAY()),1),B2:B100)/
COUNTIF(A2:A100,">"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)

or,

=AVERAGE(IF(A2:A100>DATE(YEAR(TODAY()),MONTH(TODAY()),1),B2:B100))

The 2nd formula is an array formula, and must be entered with
CTRL+SHIFT+ENTER.

If you want to exclude the data from July, 2004, you need to modify the 1st
formula to subtract any values where the date is