I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

On Sun, 21 Dec 2008 04:38:02 -0800, wannabe68
wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.


The following **array-entered** formula should do that:

(see below for important notes)

=AVERAGE(OFFSET(A1,-1+MAX(ISNUMBER(rng)*ROW(rng)),0,-7))

If there might be blanks (or cells containing non-numeric information to be
ignored, then try:

=AVERAGE(N(OFFSET(A1,-1+LARGE(ISNUMBER(rng)*ROW(rng),{1,2,3,4,5,6,7}),0) ))

To **array-enter** a formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula if you did it correctly.

Note that rng is a reference to the column in which you have the values. If
using a version of Excel prior to 2007, rng cannot refer to the entire column,
but could be as large as A1: A65535.

Note that A1 represents the topmost cell in rng

--ron

Sorry Mike. I have "semi" learned Excel through trial and error.
Can I copy and paste your formula on the "fx" line? if so, where does my
resulting average show? When I tried this I got the "circular" error message.



"Mike H" wrote:

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

Hi,

It can go anywhere on the same sheet as you data except in the same column
as your data. Copy the formula from this post then click into the formula bar
and tap CTRL+V to paste it in then careafully read the instructions below for
entering an array formula and with the cursor still in the formula bar follow
those instructions.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

Sorry Mike. I have "semi" learned Excel through trial and error.
Can I copy and paste your formula on the "fx" line? if so, where does my
resulting average show? When I tried this I got the "circular" error message.



"Mike H" wrote:

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

Mike
Think I am getting close. Went to an empty cell, and I get the error message
#NAME?
There is nothing else in that column.
Where did I go wrong?

"Mike H" wrote:

Hi,

It can go anywhere on the same sheet as you data except in the same column
as your data. Copy the formula from this post then click into the formula bar
and tap CTRL+V to paste it in then careafully read the instructions below for
entering an array formula and with the cursor still in the formula bar follow
those instructions.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

Sorry Mike. I have "semi" learned Excel through trial and error.
Can I copy and paste your formula on the "fx" line? if so, where does my
resulting average show? When I tried this I got the "circular" error message.



"Mike H" wrote:

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

Hi,

The #NAME error means Excel didn't recognise something in the formula. Don't
retype the formula copy it and paste it. here#s the formula again

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)

Mike

"wannabe68" wrote:

Mike
Think I am getting close. Went to an empty cell, and I get the error message
#NAME?
There is nothing else in that column.
Where did I go wrong?

"Mike H" wrote:

Hi,

It can go anywhere on the same sheet as you data except in the same column
as your data. Copy the formula from this post then click into the formula bar
and tap CTRL+V to paste it in then careafully read the instructions below for
entering an array formula and with the cursor still in the formula bar follow
those instructions.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

Sorry Mike. I have "semi" learned Excel through trial and error.
Can I copy and paste your formula on the "fx" line? if so, where does my
resulting average show? When I tried this I got the "circular" error message.



"Mike H" wrote:

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

Got it Mike. Thanks for all your help.

"Mike H" wrote:

Hi,

The #NAME error means Excel didn't recognise something in the formula. Don't
retype the formula copy it and paste it. here#s the formula again

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)

Mike

"wannabe68" wrote:

Mike
Think I am getting close. Went to an empty cell, and I get the error message
#NAME?
There is nothing else in that column.
Where did I go wrong?

"Mike H" wrote:

Hi,

It can go anywhere on the same sheet as you data except in the same column
as your data. Copy the formula from this post then click into the formula bar
and tap CTRL+V to paste it in then careafully read the instructions below for
entering an array formula and with the cursor still in the formula bar follow
those instructions.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

Sorry Mike. I have "semi" learned Excel through trial and error.
Can I copy and paste your formula on the "fx" line? if so, where does my
resulting average show? When I tried this I got the "circular" error message.



"Mike H" wrote:

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.

I began to think we wouldn't get there. Glad I could help.

"wannabe68" wrote:

Got it Mike. Thanks for all your help.

"Mike H" wrote:

Hi,

The #NAME error means Excel didn't recognise something in the formula. Don't
retype the formula copy it and paste it. here#s the formula again

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)

Mike

"wannabe68" wrote:

Mike
Think I am getting close. Went to an empty cell, and I get the error message
#NAME?
There is nothing else in that column.
Where did I go wrong?

"Mike H" wrote:

Hi,

It can go anywhere on the same sheet as you data except in the same column
as your data. Copy the formula from this post then click into the formula bar
and tap CTRL+V to paste it in then careafully read the instructions below for
entering an array formula and with the cursor still in the formula bar follow
those instructions.

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

Sorry Mike. I have "semi" learned Excel through trial and error.
Can I copy and paste your formula on the "fx" line? if so, where does my
resulting average show? When I tried this I got the "circular" error message.



"Mike H" wrote:

ah you qanted yo oognore blanks not zero try this instead

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<""),7)):A1000)


Mike

"Mike H" wrote:

Hi,

Try this. Ensure the 1000 is large enought to catch all of your number range

=AVERAGE(INDEX(A1:A1000,LARGE(ROW(A1:A1000)*(A1:A1 000<0),7)):A1000)

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"wannabe68" wrote:

I have a column of numbers, some blank, and continually adding additional
numbers to the column. What would the formula be to continually averge the
last 7 numbers entered?
Been up all night trying to figure it out. Thank you for your help.