Excel formula help

[elcracko]

I have a worksheet where column A holds dates todays date would be the
below yesterdays and so on) and coumn B holds a percentage. On a
separate page, I have an average field that is currently displaying a
fixed average of 90 days from the other sheet. I would like a formula
so the number of days can be input and then it would return the average
for the last x days. I have tried numerous forulas but it seems that
average cannot have a formula result as the arguments.

Any help is much appreciated.

 

[Guest]

try
=average(offset(Sheet1!B1,0,0,X,1))
X is th enumber of days you want to average or can be a cell reference with
the number in it.

 

[Biff]

Hi!

Here's one way:

Assume the TRUE dates are in sheet1 column A, A1:An AND THERE ARE NO EMPTY
ROWS WITHIN THE RANGE!

On sheet2 cell A1 holds the variable for the last number of days to average:

Sheet2 A1 = 5 (average the LAST 5 days)

=AVERAGE(OFFSET(Sheet1!A1,COUNT(Sheet1!A:A)-1,,-1*A1))

Biff