Floating 6 month average

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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
 
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.
 
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
 
-----Original Message-----
Why would you not just take (last value - first value) / 6 ie
(9,260.8-1,338.29)/6
Click to expand...

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??
Click to expand...

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
Click to expand...

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.
 
Back
Top