Excel 2007 average formula

[Tony O]

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]

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.

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]

hi Tony,

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

--
isabelle



Le 2012-05-02 19:44, Tony O a écrit :

 

[isabelle]

sorry,

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

--
isabelle



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

 

[umarovjasur]

sorry,



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



--

isabelle







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

Or perhaps use =SUBTOTAL(101,E:E) and hide rows that shouldn't be included?

http://excelformulaslist.com/
http://www.excelformulaslist.com/math-and-trigonometry/subtotal