Automaticly changing average function target cells
 

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.

Automaticly changing average function target cells
 

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

Automaticly changing average function target cells
 

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.

Automaticly changing average function target cells
 

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.

Automaticly changing average function target cells
 

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.

Automaticly changing average function target cells
 

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.

Automaticly changing average function target cells
 

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.