Monthly Averages

[Tom Raab]

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 less than or
equal to 183 days. The correct number for cell C20 is
$1,339.67 per month based on the info for 1/2/03 and 7/2/02
180 days earlier, and calculated by (9260.80-1338.29)/180
days * 365.25 /12.

A B C
1 Date Total Cost Cost per Month
6 Month Average
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
=(B20-B6)/DAYS360(A6,A20)*365.25/12
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

..