Need formula to calculate average number of days

The worksheet is defined as follows: In cell A2 is the date 5-10-2010, in cell A3 is the date 5-14-2010, in cell A4 is the date 8-31-2010 in cell A5 is the date 10-17-2010. In cell B2 is the value 1, in cell B3 is the value 2, in cell B4 is the value 3, in cell B5 is the value 4, and in cell B6 is the value 5. In cells C2 through C6 a value of 1 is entered in each cell. In cell A1 is the word Date, in cell B1 is the word Days, and in Cell C1 is the word Count. In cell A9 is the date 5-10-2010, in cell A10 is the date 5-1-2010, and in cell A11 is the date 10-1-2009. The date format is entered as month-day-year. Here's what I need:

In cell B9 I need a formula that will calculate the average number of days from column B from the beginning date entered in A9 plus 6 days (a week).

In cell B10 I need a formula that will calculate the average number of days from column B from the beginning date entered in A10 plus 30 days (the month of may for the year 2010).

In cell B11 I need a formula that will calculate the average number of days from column B from the beginning date entered in A11 plus 364 days (the fiscal year ending in Sep 30, 2010).

So, the result in cell B9 should calculate the average number of days based on 5-10-2010 and 5-14-2010, as these are the only two dates encompassed in the week time frame for cell B9.

For B10, it would include only 5-10-2010 and 5-14-2010. For B11 it would include all of the dates with the exception of the last date (10-17-2010).

Thank you,

Bruce

I am confused Bruce. If B2:B6 were dates I could see how there would be a number of days from A9 to B2:B6, but as B2:B6 is just numbers how does that equate to number of days?

Bob,

In column A are obviously the date entries. In column B are the number of days that it took to do a certain task. Column A represents the end date in a series of tasks. Column C is simply a counter column where basically each row contains the value 1. I need for the formula to look at the dates and determine which dates are equal to 5/10, 5/11, 5/12, 5/13, 5/14, 5/15 and 5/16 and based on this criteria determine that 5/10 and 5/14 in cells A2 and A3 qualify, and then take the values in cell B2 and B2 (1 and 2), and calculate the average of these values. So, the result would be 1+2=3 ÷ 2 = 1.5.

The real world example is of course more complex, which is why I decided to simplify my requirements in this forum.

I hope this helps.

Bruce

Let me re-phrase the problem. The worksheet is defined as follows: In cell A2 is the date 5-10-2010, in cell A3 is the date 5-14-2010, in cell A4 is the date 8-31-2010 in cell A5 is the date 10-17-2010, and in Cell A6 is the date 10-18-2010. In cell B2 is the value 10313, in cell B3 is the value 20515, in cell B4 is the value 30888, in cell B5 is the value 4000, and in cell B6 is the value 5000000. In cells C2 through C6 a value of 1 is entered in each cell. In cell A1 is the word Date, in cell B1 is the word Pernicious Knids, and in Cell C1 is the word Count. In cell A9 is the date 5-10-2010, in cell A10 is the date 5-1-2010, and in cell A11 is the date 10-1-2009. The date format is entered as month-day-year. Here's what I need:

The data table is graphically portrayed (range A1:C6) as follows:

Date                 Pernicious Knids              Count

5-10-2020           10313                           1

5-14-2010           20515                           1

8-31-2010           30888                            1

10-17-2010         4000                             1

10-18-2010         5000000                        1

A1:C6 to recap graphically:

• Date              Pernicious Knids       Count
• 5-10-2010        10313                    1
• 5-14-2010        20515                    1
• 8-31-2010        30888                    1
• 10-17-2010         4000                   1
• 10-18-2010    5000000                   1

How about these array formulae

=AVERAGE(IF($A$2:$A$6<$A9+6,$B$2:$B$6))

=AVERAGE(IF($A$2:$A$6<$A10+30,$B$2:$B$6))

=AVERAGE(IF($A$2:$A$6<$A11+364,$B$2:$B$6))

Bob,

This is precisely the elegant, simple, yet effective solution that I was confident that you would provide. You have saved me much time and trouble.

Sincerely,

Bruce