Excel 2007 : Column to formulate an average of the current four dates

I have my dates on a row and I have the data under each date that pertains to it. Trying to figure out the formula so that the cell will only pick up the four most current dates even as I continue to enter data going forward. Any suggestions.


jmarrer0

Try:

=AVERAGE(IF($A$1:$J$1>=LARGE($A$1:$J$1,4),$A$2:$J$2))

where A1:J1 is the top row range, and A2:J2 is the inputs range.

if you want to exclude blanks in A2:J2

=AVERAGE(IF($A$1:$J$1>=LARGE($A$1:$J$1,4),IF($A$2:$J$2<>"",$A$2:$J$2)))

either of these formulas are to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work properly

From PM:

Thank for the information. I am now getting an error message (#VALUE!) in the cell where I want the formula to give me my answer. In row #1 I have all my Sunday dates (i.e-8/7, 8/14, 8/21, etc). In row #2 I have a numbered value under each date as it pertains to what happened within that date. At the end of the row, I have a cell in which I only want to capture the four most current dates with data. I have my dates all the way through the end of the year 2011. Every time I enter data under a new date, I would like the formula to only pick up the current four date with data.

After you enter the formula, hold the CTRL and SHIFT keys down, then press ENTER. Do you now get a numeric result?

I replied to that above.

You are right... try:

=AVERAGE(IF(A2:W2<>"",IF(COLUMN(A2:W2)>=LARGE(IF(A2:W2,COLUMN(A2:W2)),4),A2:W2)))

confirmed with CTRL+SHIFT+ENTER