averaging last entries

W

wannabe68

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.
 
M

Mike H

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:A1000<>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
 
M

Mike H

ah you qanted yo oognore blanks not zero try this instead

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


Mike
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
W

wannabe68

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.
 
M

Mike H

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
 
W

wannabe68

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?
 
M

Mike H

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:A1000<>""),7)):A1000)

Mike
 
W

wannabe68

Got it Mike. Thanks for all your help.

Mike H said:
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:A1000<>""),7)):A1000)

Mike
Click to expand...
 
Top