Dynamic reference in OFFSET function

[manu]

Hello All,

I am trying to use a dynamic reference for an OFFSET fucntion but am
not being able to.

The following is the format of my data:

A B
Day Sales
1 10
2 20
3 12
4 24
5 21

A new row is added to this sheet with every day of sales.
Now I need to create a report on the average sales in the last 2 days

I had the following from when I was calculating average sales (on all
days)
=AVERAGE(OFFSET(Data!$B$2,0,0,COUNT(Data!$B:$B),1))

Now to calculate the last 2 day average i will need a dynamic value
for the "reference"
argument of the OFFSET function. I cant seem to figure this one.

Your help will be greatly appreciated!

Thanks!
manu

 

[Duke Carey]

If the days in column A are an uninterruped series you can use

=AVERAGE(OFFSET($B$2,MAX($A$2:$A$100)-2,0,2,1))

Otherwise,

=AVERAGE(OFFSET($B$2,COUNT($B$2:$B$100)-2,0,2,1))