Average last 6

[Kwanjangnim]

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?

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Kwanjangnim]

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:76)*(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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Ragdyer]

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:70)*(B7:B76<>""),6))))
--
HTH,

RD

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


"Kwanjangnim" <[email protected]> wrote in message
news:[email protected]...
> 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:76)*(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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

[davidm]

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]

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.

[Bob Phillips]

Hi David,

=AVERAGE(A65336:INDEX(A1:A65336,SUMPRODUCT(LARGE(ROW(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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Kwanjangnim]

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:70)*(B7:B76<>""),6))))
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
>
> "Kwanjangnim" <[email protected]> wrote in message
> news:[email protected]...
> > 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:76)*(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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > >
> > >
> > >
>
>

[RagDyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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:70)*(B7:B76<>""),6))))
> > --
> > HTH,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> >
> >
> > "Kwanjangnim" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:76)*(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" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >

[Kwanjangnim]

yeah there are formulas in b7:b76
"=IF(ISNA(N18+(SUM(I18:M18))),"",N18+(SUM(I18:M18)))"

=AVERAGE(B76:INDEX(B7:B76,SUMPRODUCT(LARGE(ROW(1:70)*(B7:B76<>""),6)))) is
what you revised, but i just wanted to check that "(ROW(1:70)" not ment to be
"(ROW(7:76)" it doesn't matter anyway coz i still get "div/0" error

will use the "=ISNUMBER(B7)&"-"&LEN(B7)" to validate col b and get back to you

thanks for your help so far


"RagDyer" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:70)*(B7:B76<>""),6))))
> > > --
> > > HTH,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > >
> > >
> > > "Kwanjangnim" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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:76)*(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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>

[Kwanjangnim]

update: not that it matter for the examples i'm testing but in my worksheet
b7:b76 is actually r7:r76

and the validation formula returned "false-0" "=ISNUMBER(r18)&"-"&LEN(r18)"

cheers
colin

"RagDyer" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:70)*(B7:B76<>""),6))))
> > > --
> > > HTH,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > >
> > >
> > > "Kwanjangnim" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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:76)*(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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>

[RagDyeR]

You got a return of "False-0" for R18?!?!?!

When you just said that those cells contained the formula:
"=IF(ISNA(N18+(SUM(I18:M18))),"",N18+(SUM(I18:M18)))"

Does that means that you have a #N/A error in the I18:N18 range, and R18
looks empty?

Also, is the entire R6:R76 range populated with the IF() formula?

Perhaps you might wish to send me a copy of your sheet.

Cut out cutout from my address.


Regards,

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

"Kwanjangnim" <[email protected]> wrote in message
news:[email protected]...
update: not that it matter for the examples i'm testing but in my worksheet
b7:b76 is actually r7:r76

and the validation formula returned "false-0" "=ISNUMBER(r18)&"-"&LEN(r18)"

cheers
colin

"RagDyer" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:70)*(B7:B76<>""),6))))
> > > --
> > > HTH,
> > >
> > > RD
> > >
> >
>
> --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may
benefit
> !
> >
>
> --------------------------------------------------------------------------
> -
> > >
> > >
> > > "Kwanjangnim" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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:76)*(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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>

[RagDyeR]

The sheet is on the way back to you.

I didn't change anything in your sheet *except* what was in Column R.
In fact, I didn't even change R, but added revised formulas to Column S,
right along side.

I didn't get into the intricacies of your sheet and formulas, so make sure
that the change I made won't screw anything else up that I might not have
noticed.

It seems that the error message generated by your Sum formula caused all the
problem.

I *didn't* see that #DIV/0 error that you kept mentioning about in your
posts.

--

Regards,

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


"RagDyeR" <[email protected]> wrote in message
news:[email protected]...
You got a return of "False-0" for R18?!?!?!

When you just said that those cells contained the formula:
"=IF(ISNA(N18+(SUM(I18:M18))),"",N18+(SUM(I18:M18)))"

Does that means that you have a #N/A error in the I18:N18 range, and R18
looks empty?

Also, is the entire R6:R76 range populated with the IF() formula?

Perhaps you might wish to send me a copy of your sheet.

Cut out cutout from my address.


Regards,

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

"Kwanjangnim" <[email protected]> wrote in message
news:[email protected]...
update: not that it matter for the examples i'm testing but in my worksheet
b7:b76 is actually r7:r76

and the validation formula returned "false-0" "=ISNUMBER(r18)&"-"&LEN(r18)"

cheers
colin

"RagDyer" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:70)*(B7:B76<>""),6))))
> > > --
> > > HTH,
> > >
> > > RD
> > >
> >
>
> --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may
benefit
> !
> >
>
> --------------------------------------------------------------------------
> -
> > >
> > >
> > > "Kwanjangnim" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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:76)*(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" <[email protected]> wrote in
> message
> > > > > news:[email protected]...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
>
>