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.

=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." wrote in message
...
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.

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


"Bob Phillips" wrote:

=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." wrote in message
...
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'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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil H." wrote in message
...
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


"Bob Phillips" wrote:

=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." wrote in message
...
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.

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.

"RagDyer" wrote:

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil H." wrote in message
...
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


"Bob Phillips" wrote:

=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." wrote in message
...
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.

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

"Phil H." wrote:

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.

"RagDyer" wrote:

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.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil H." wrote in message
...
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


"Bob Phillips" wrote:

=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." wrote in message
...
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.

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Phil H." wrote in message
...
Never Mind, I Got It. Sorry.
=AVERAGE(INDEX(I4:I14,LARGE(ROW(1:11)*(I4:I14<"") ,6)):I14)

"Phil H." wrote:

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.

"RagDyer" wrote:

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.

--
HTH,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit !

--------------------------------------------------------------------------
-
"Phil H." wrote in message
...
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


"Bob Phillips" wrote:


=AVERAGE(INDEX(1:1,,SUMPRODUCT(LARGE(COLUMN(1:1)*( 1:1<""),1))):INDEX(1:1,,S
UMPRODUCT(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." wrote in message
...
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.