Excel - Excel 2007 average formula

Asked By Tony O on 02-May-12 07:44 PM
```I am using Excel 2007 for my reports.

I have a cell to find the average of cell E10 and the five cells above
it.

My formula is this:
=AVERAGE(E6:E10)

Every day I add a new row with new data.  In this example it will be
in E11.
How do I get the average formula to automatically recalculate so
with the new data it will calculate cell E11 and the five cells above
it?
The formula should now be:
=average(E7:E11)

Every day I have new sales data that moves the data.

If you have any thoughts on this, it will be appreciated.  I have
multiple formulas that I have to update
on a daily basis.```

Ron Rosenfeld replied to Tony O on 02-May-12 08:36 PM
```If the only thing in column E is the sales data, and if you want to average the last five contiguous entries in that column, you could use:

=AVERAGE(OFFSET(\$E\$1,LOOKUP(2,1/ISNUMBER(\$E:\$E),ROW(\$E:\$E))-1,0,-5))

But if you have numeric data below the last row of relevant data, you will have to explain your set up better.```
isabelle replied to Tony O on 02-May-12 10:12 PM
```hi Tony,

=MOYENNE(INDIRECT("E"&LIGNE()-5&":E"&LIGNE()-1))

--
isabelle

Le 2012-05-02 19:44, Tony O a ?crit :```
isabelle replied to isabelle on 02-May-12 10:29 PM
```sorry,

=AVERAGE(INDIRECT("E"&ROW()-5&":E"&ROW()-1))

--
isabelle

Le 2012-05-02 22:12, isabelle a ?crit :```