Floating 6 month average

[Tom]

In the following listing, I am attempting to build column C
with the average cost/month for the last 6 months on each
row. The formula in Row 20 produces the correct
calculation, but I can't figure out how to automatically
find the last date going up column A which is .le. 183 days.

A B C
1 Date Total Cost 6 Month Average
Cost per Month
2
3 05/13/02 0.09
4 06/03/02 26.25
5 06/04/02 1295.17
6 07/02/02 1338.29
7 07/22/02 2607.21
8 08/01/02 2648.01
9 08/21/02 3916.93
10 09/03/02 5075.77
11 09/03/02 5082.97
12 10/01/02 5082.97
13 10/01/02 5127.00
14 10/21/02 6395.92
15 11/04/02 6460.80
16 11/21/02 7729.72
17 12/06/02 7619.00
18 12/06/02 9092.92
19 12/02/02 9113.59
20 01/02/03 9260.80 =6*(I20-I6)/DAYS360(A6,A20)
21 01/20/03 10734.72 Ditto
22 02/03/03 10977.39 Ditto
23 02/04/03 10977.39 Ditto
24 02/21/03 12451.31 Ditto
25 03/03/03 13514.68 Ditto
26 03/03/03 13552.65 Ditto

 

[Jason Morin]

Try this in C20 and fill down:

=AVERAGE(OFFSET(B20,,,-(COUNT($A$3:A20)-MATCH(DATE(YEAR
(A20),MONTH(A20)-6,DAY(A20)),$A$3:A20,1)+1)))

HTH
Jason
Atlanta, GA

 

[Tom Raab]

Not quite.

I am NOT looking for a 6 month average of column B.

I am looking for the average Cost per Month over each 6
month period. That is, the ending Total Cost (on 1/2/03)
minus the starting Total Cost (180 days earlier on 7/2/02)
all divided by the inclusive days to figure the average
cost per day. That amount should then be multiplied by
365.25/12 to make it a monthly average. (My earlier
calculation was wrong here.) The correct number should be
$1,339.65 not 5770.13 from your formula.

 

[Ken Wright]

Why would you not just take (last value - first value) / 6 ie
(9,260.8-1,338.29)/6

The 6 for 6 months is generally going to be just as correct or incorrect as
using an average 365.25.

Also, you don't say which way you want to go if there is not an exact 6 months
between the dates - higher or lower??

Assuming you wanted the last value that was at least 6 months prior, then in
C20, put the following and copy down.

=(B20-VLOOKUP(DATE(YEAR(A20),MONTH(A20)-6,DAY(A20)),$A$3:B20,2))/6

 

[Tom Raab]

-----Original Message-----
Why would you not just take (last value - first value) / 6 ie
(9,260.8-1,338.29)/6

Approximately right, but the dates are not usually 182.625
days apart. Therefore, the adjustment is made to the
actual day count between them. There may be differences of
a few weeks.

The 6 for 6 months is generally going to be just as correct or incorrect as
using an average 365.25.

Also, you don't say which way you want to go if there is not an exact 6 months
between the dates - higher or lower??

My original specification said ".le. 183 days."

Assuming you wanted the last value that was at least 6 months prior, then in
C20, put the following and copy down.

=(B20-VLOOKUP(DATE(YEAR(A20),MONTH(A20)-6,DAY(A20)),$A$3:B20,2))/6

Your formula came up with $1320.42 which is about $19 shy
due the 180 day count needing to be adjusted to a full 6
months.

Were getting closer. Thanks for the idea.