Automaticly changing average function target cells

[Phil H.]

I am looking to create a function to only average the last 6 entries in a
list of numbers w/out changing the target cells of the AVERAGE function on a
monthly bases.
Example: I want to track scores on a monthly bases but only want the average
of the last six months worth of scores.

 

[Bob Phillips]

=AVERAGE(INDEX(1:1,,SUMPRODUCT(LARGE(COLUMN(1:1)*(1:1<>""),1))):INDEX(1:1,,SUMPRODUCT(LARGE(COLUMN(1:1)*(1:1<>""),6))))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Phil H.]

This creates a #DIV Error.
Please explain this function so I may find the error
Here is what I am Attempting to do.

A
Jan 5
Feb 4
Mar 3
Apr 2
May 3
June 4
July
Avg X

I want X to equal Average(A1:A6) but to automaticly change to Average(A2:A7)
when an the value of A7 is changed from null to an numerical value

 

[RagDyer]

Bob's formula was configured to work along Row1, *across* columns.

Try this *array* formula, where the months are in Column A, and the values
are in Column B.
This is sized for 2 years, going from B2 down to B25:

=AVERAGE(INDEX(B2:B25,LARGE(ROW(1:24)*(B2:B25<>""),6)):B25)

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

 

[Phil H.]

OK, Getting closer. The values in I4:I14 are as follows
1041.25, 1205.00, 1238.33, 1200.00, 1190.00, 1315.63 followed by 5 null cells.

the array that I edited to be:
=AVERAGE(INDEX(I4:I14,LARGE(ROW(4:14)*(I4:I14<>""),6)):I14)

returns a value of 1235.208333, though it should be 1198.368 when done
manually using SUM(I4:I9)/6
The Columns next to this one do have values that might be being pulled, and
also the cells I4:I14 do include funtions them selves. Though since I do not
understand the Large or Row functions in this array I am having a hard time
T/Sing the issue. Please Help.

 

[Phil H.]

Never Mind, I Got It. Sorry.
=AVERAGE(INDEX(I4:I14,LARGE(ROW(1:11)*(I4:I14<>""),6)):I14)

 

[Ragdyer]

Glad you were able to work it out.
Appreciate the feed-back.