hi all
i've had a look through all the other threads but haven't bee able to find
the solution i'm looking for, basically i have a column which has a numeric
value entered (in each row) according to results in other cells. see example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results (a2:a7), this
column will be updated as new results are added, therefore the range that
needs to be averaged will constantly change to so that ONLY the last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK) will need to
be ignored. can anyone help?

=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwanjangnim" wrote in message
...
hi all
i've had a look through all the other threads but haven't bee able to find
the solution i'm looking for, basically i have a column which has a
numeric
value entered (in each row) according to results in other cells. see
example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results (a2:a7), this
column will be updated as new results are added, therefore the range that
needs to be averaged will constantly change to so that ONLY the last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK) will need
to
be ignored. can anyone help?

thanks for the code, it worked well when i tested it in a blank worksheet,
however for some reason it doesn't work when placed in my worksheet, i keep
getting a 'divide by zero error' but all i changed from your formula was the
col range and the start row and end row no.s
=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6)))) the
empty cells that are awaiting results from other cells have been formulated
to display blank but i keep getting 'DIV/0' can you sort this for me?


"Bob Phillips" wrote:

=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwanjangnim" wrote in message
...
hi all
i've had a look through all the other threads but haven't bee able to find
the solution i'm looking for, basically i have a column which has a
numeric
value entered (in each row) according to results in other cells. see
example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results (a2:a7), this
column will be updated as new results are added, therefore the range that
needs to be averaged will constantly change to so that ONLY the last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK) will need
to
be ignored. can anyone help?

I must say that Bob did a good job with this formula!

However, there's just a coincidence between the range in his example and the
actual size of the range itself.

Your range is 70 rows, so revise your formula to this:

=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6))))
--
HTH,

RD

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


"Kwanjangnim" wrote in message
...
thanks for the code, it worked well when i tested it in a blank worksheet,
however for some reason it doesn't work when placed in my worksheet, i
keep
getting a 'divide by zero error' but all i changed from your formula was
the
col range and the start row and end row no.s
=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6))))
the
empty cells that are awaiting results from other cells have been
formulated
to display blank but i keep getting 'DIV/0' can you sort this for me?


"Bob Phillips" wrote:


=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwanjangnim" wrote in message
...
hi all
i've had a look through all the other threads but haven't bee able to
find
the solution i'm looking for, basically i have a column which has a
numeric
value entered (in each row) according to results in other cells. see
example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results (a2:a7),
this
column will be updated as new results are added, therefore the range
that
needs to be averaged will constantly change to so that ONLY the last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK) will
need
to
be ignored. can anyone help?

Just curious. How can Bob's formula be tweaked to handle a generic
situation where* the last 6 rows in Column A* are always averaged.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=389720

Hi David,

=AVERAGE(A65336:INDEX(A1:A65336,SUMPRODUCT(LARGE(R OW(1:65336)*(A1:A65336<""
),6))))

although you wont want many of these in your spreadsheet <g

--

HTH

RP
(remove nothere from the email address if mailing direct)


"davidm" wrote in
message ...

Just curious. How can Bob's formula be tweaked to handle a generic
situation where* the last 6 rows in Column A* are always averaged.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=389720

Just curious. How can Bob's formula be tweaked to handle a generic
situation where* the last 6 rows in Column A* are always averaged.


+-------------------------------------------------------------------+
|Filename: encdcpt.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3621 |
+-------------------------------------------------------------------+

--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=389720

I still have the 'Div/0' error that needs fixing any suggestion, please read
previous thread for details

"Ragdyer" wrote:

I must say that Bob did a good job with this formula!

However, there's just a coincidence between the range in his example and the
actual size of the range itself.

Your range is 70 rows, so revise your formula to this:

=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6))))
--
HTH,

RD

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


"Kwanjangnim" wrote in message
...
thanks for the code, it worked well when i tested it in a blank worksheet,
however for some reason it doesn't work when placed in my worksheet, i
keep
getting a 'divide by zero error' but all i changed from your formula was
the
col range and the start row and end row no.s
=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6))))
the
empty cells that are awaiting results from other cells have been
formulated
to display blank but i keep getting 'DIV/0' can you sort this for me?


"Bob Phillips" wrote:


=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwanjangnim" wrote in message
...
hi all
i've had a look through all the other threads but haven't bee able to
find
the solution i'm looking for, basically i have a column which has a
numeric
value entered (in each row) according to results in other cells. see
example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results (a2:a7),
this
column will be updated as new results are added, therefore the range
that
needs to be averaged will constantly change to so that ONLY the last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK) will
need
to
be ignored. can anyone help?

If you revised your formula as I suggested, and you're still getting that
#DIV/0! error, then I would guess that your numbers are *not numbers*.

Now, your OP said that these numbers were the *results* from other cells.

Are there formulas in B7:B76?

If so, post back with the formulas.

Also, you could try this formula to test the contents of Column B:

=ISNUMBER(B7)&"-"&LEN(B7)

Copy down and make sure that you see "True", and that the number returned
matches the visible characters in the formula bar ( i.e formats - $ - don't
count)

--
Regards,

RD

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

"Kwanjangnim" wrote in message
...
I still have the 'Div/0' error that needs fixing any suggestion, please
read
previous thread for details

"Ragdyer" wrote:

I must say that Bob did a good job with this formula!

However, there's just a coincidence between the range in his example and
the
actual size of the range itself.

Your range is 70 rows, so revise your formula to this:

=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:7 0)*(B7:B76<""),6))))
--
HTH,

RD


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

--------------------------------------------------------------------------
-


"Kwanjangnim" wrote in message
...
thanks for the code, it worked well when i tested it in a blank
worksheet,
however for some reason it doesn't work when placed in my worksheet, i
keep
getting a 'divide by zero error' but all i changed from your formula
was
the
col range and the start row and end row no.s

=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(7:7 6)*(B7:B76<""),6))))
the
empty cells that are awaiting results from other cells have been
formulated
to display blank but i keep getting 'DIV/0' can you sort this for me?


"Bob Phillips" wrote:



=AVERAGE(A200:INDEX(A1:A200,SUMPRODUCT(LARGE(ROW(1 :200)*(A1:A200<""),6))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Kwanjangnim" wrote in
message
...
hi all
i've had a look through all the other threads but haven't bee able
to
find
the solution i'm looking for, basically i have a column which has
a
numeric
value entered (in each row) according to results in other cells.
see
example
below

A
1 l 10
2 l -10
3 l 12
4 l 0
5 l 16
6 l -11
7 l 10
8 l
9 l

i need a funchtion that will average ONLY the last 6 results
(a2:a7),
this
column will be updated as new results are added, therefore the
range
that
needs to be averaged will constantly change to so that ONLY the
last 6
entries will be averaged.
(a1:a50) will be the max range so cells with no entries (blanK)
will
need
to
be ignored. can anyone help?