Average the last 5 of a continually filling row.
 

I have 20 cells in a row (G34:Z34) with data in the first 7 cells (G:34 thru
M:34)
I would like to average the last 5 entries and continue averaging the last
five as I fill the entire row with data. I would like to expand this row to a
greater lenght in the future.
--
Geo

Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7 cells (G:34
thru
M:34)
I would like to average the last 5 entries and continue averaging the last
five as I fill the entire row with data. I would like to expand this row
to a
greater lenght in the future.
--
Geo

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" wrote in message
...
Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7 cells (G:34
thru
M:34)
I would like to average the last 5 entries and continue averaging the
last
five as I fill the entire row with data. I would like to expand this row
to a
greater lenght in the future.
--
Geo

I have tried copying the formula and pasting it to the cell where I want the
average to be displayed and I get a "0" result. I have tried entering it also
with the <shift<Ctrl<Enter also and still get the same results. There is
data in G:34-O:34.(9 cells accross row 34). Do I have to change the formula
any?

Thanks,
Geo

"Ken Wright" wrote:

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" wrote in message
...
Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7 cells (G:34
thru
M:34)
I would like to average the last 5 entries and continue averaging the
last
five as I fill the entire row with data. I would like to expand this row
to a
greater lenght in the future.
--
Geo

What cell are you putting it in?
Is there anything else at all in row 34 after the column that has the last
number in, which you said was O34
When you array entered, did you click into the formula in the formula bar,
or hit F2 to go into edit mode, and then whilst holding down CTRL+SHIFT hit
the ENTER key. if entered corrcetly the formula will display in the formula
bar with curly braces around it, eg {formula}. These braces CANNOT be
entered manually, it is not the same.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
I have tried copying the formula and pasting it to the cell where I want
the
average to be displayed and I get a "0" result. I have tried entering it
also
with the <shift<Ctrl<Enter also and still get the same results. There
is
data in G:34-O:34.(9 cells accross row 34). Do I have to change the
formula
any?

Thanks,
Geo

"Ken Wright" wrote:

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Ken Wright" wrote in message
...
Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7 cells
(G:34
thru
M:34)
I would like to average the last 5 entries and continue averaging
the
last
five as I fill the entire row with data. I would like to expand this
row
to a
greater lenght in the future.
--
Geo

I am putting the formula into cell 05. The cells that follow O34 contain
zeros. I clicked on the formula bar then hit the CTRL+SHIFT ENTER key. I did
end up with the curly brackets around the entire formula. but ended up with a
"0" result.

"Ken Wright" wrote:

What cell are you putting it in?
Is there anything else at all in row 34 after the column that has the last
number in, which you said was O34
When you array entered, did you click into the formula in the formula bar,
or hit F2 to go into edit mode, and then whilst holding down CTRL+SHIFT hit
the ENTER key. if entered corrcetly the formula will display in the formula
bar with curly braces around it, eg {formula}. These braces CANNOT be
entered manually, it is not the same.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
I have tried copying the formula and pasting it to the cell where I want
the
average to be displayed and I get a "0" result. I have tried entering it
also
with the <shift<Ctrl<Enter also and still get the same results. There
is
data in G:34-O:34.(9 cells accross row 34). Do I have to change the
formula
any?

Thanks,
Geo

"Ken Wright" wrote:

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Ken Wright" wrote in message
...
Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7 cells
(G:34
thru
M:34)
I would like to average the last 5 entries and continue averaging
the
last
five as I fill the entire row with data. I would like to expand this
row
to a
greater lenght in the future.
--
Geo

As per the caveat in the first note:-

Assumes nothing else in row 34 after your last number

Now need some more info regarding your data. Can the value be 0 for any
period included within the one you are looking at?

Can any number be less than 0?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
I am putting the formula into cell 05. The cells that follow O34 contain
zeros. I clicked on the formula bar then hit the CTRL+SHIFT ENTER key. I
did
end up with the curly brackets around the entire formula. but ended up
with a
"0" result.

"Ken Wright" wrote:

What cell are you putting it in?
Is there anything else at all in row 34 after the column that has the
last
number in, which you said was O34
When you array entered, did you click into the formula in the formula
bar,
or hit F2 to go into edit mode, and then whilst holding down CTRL+SHIFT
hit
the ENTER key. if entered corrcetly the formula will display in the
formula
bar with curly braces around it, eg {formula}. These braces CANNOT be
entered manually, it is not the same.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Geo" wrote in message
...
I have tried copying the formula and pasting it to the cell where I
want
the
average to be displayed and I get a "0" result. I have tried entering
it
also
with the <shift<Ctrl<Enter also and still get the same results.
There
is
data in G:34-O:34.(9 cells accross row 34). Do I have to change the
formula
any?

Thanks,
Geo

"Ken Wright" wrote:


=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)


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

"Ken Wright" wrote in message
...
Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)


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

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7
cells
(G:34
thru
M:34)
I would like to average the last 5 entries and continue
averaging
the
last
five as I fill the entire row with data. I would like to expand
this
row
to a
greater lenght in the future.
--
Geo

Ken,

Maybe...

=AVERAGE(OFFSET(G34,,MATCH(2,1/(G34:Z34<0))-1,,-5))

is what Geo is looking for.

The formula must be confirmed with control+shift+enter.

Note also that the formula does not check whether there is a sufficent
amount of data points in the range of interest.

Geo wrote:
I am putting the formula into cell 05. The cells that follow O34 contain
zeros. I clicked on the formula bar then hit the CTRL+SHIFT ENTER key. I did
end up with the curly brackets around the entire formula. but ended up with a
"0" result.

"Ken Wright" wrote:


What cell are you putting it in?
Is there anything else at all in row 34 after the column that has the last
number in, which you said was O34
When you array entered, did you click into the formula in the formula bar,
or hit F2 to go into edit mode, and then whilst holding down CTRL+SHIFT hit
the ENTER key. if entered corrcetly the formula will display in the formula
bar with curly braces around it, eg {formula}. These braces CANNOT be
entered manually, it is not the same.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...

I have tried copying the formula and pasting it to the cell where I want

the

average to be displayed and I get a "0" result. I have tried entering it

also

with the <shift<Ctrl<Enter also and still get the same results. There

is

data in G:34-O:34.(9 cells accross row 34). Do I have to change the

formula

any?

Thanks,
Geo

"Ken Wright" wrote:


=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


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

--

It's easier to beg forgiveness than ask permission :-)

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

--

"Ken Wright" wrote in message
.. .

Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


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

--

It's easier to beg forgiveness than ask permission :-)

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

--

"Geo" wrote in message
...

I have 20 cells in a row (G34:Z34) with data in the first 7 cells

(G:34

thru

M:34)
I would like to average the last 5 entries and continue averaging

the

last

five as I fill the entire row with data. I would like to expand this

row

to a

greater lenght in the future.
--
Geo

Yes, There is a possibility that a number in the 34 row may be less than zero
in the future.

"Ken Wright" wrote:

As per the caveat in the first note:-

Assumes nothing else in row 34 after your last number

Now need some more info regarding your data. Can the value be 0 for any
period included within the one you are looking at?

Can any number be less than 0?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
I am putting the formula into cell 05. The cells that follow O34 contain
zeros. I clicked on the formula bar then hit the CTRL+SHIFT ENTER key. I
did
end up with the curly brackets around the entire formula. but ended up
with a
"0" result.

"Ken Wright" wrote:

What cell are you putting it in?
Is there anything else at all in row 34 after the column that has the
last
number in, which you said was O34
When you array entered, did you click into the formula in the formula
bar,
or hit F2 to go into edit mode, and then whilst holding down CTRL+SHIFT
hit
the ENTER key. if entered corrcetly the formula will display in the
formula
bar with curly braces around it, eg {formula}. These braces CANNOT be
entered manually, it is not the same.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Geo" wrote in message
...
I have tried copying the formula and pasting it to the cell where I
want
the
average to be displayed and I get a "0" result. I have tried entering
it
also
with the <shift<Ctrl<Enter also and still get the same results.
There
is
data in G:34-O:34.(9 cells accross row 34). Do I have to change the
formula
any?

Thanks,
Geo

"Ken Wright" wrote:


=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-(MIN(COUNTA(G34:IV34),5))))

Will handle it when you have less than 5 values in your range

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)


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

"Ken Wright" wrote in message
...
Anywhere but in G34:IV34

=AVERAGE(OFFSET($F$34,,COUNTA(G34:IV34),,-5))

Assumes nothing else in row 34 after your last number

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)


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

"Geo" wrote in message
...
I have 20 cells in a row (G34:Z34) with data in the first 7
cells
(G:34
thru
M:34)
I would like to average the last 5 entries and continue
averaging
the
last
five as I fill the entire row with data. I would like to expand
this
row
to a
greater lenght in the future.
--
Geo

Hi Aladin, given the OPs last reply to me

Yes, There is a possibility that a number in the 34 row may be less than
zero
in the future.

I'd now be dubious about relying on anything other than manually telling it
how many data points to cater for. If the value can be greater than 0 and
less than 0 then I have to believe it is possible to actually be 0. If he's
100% sure it can't then that should do him though :-)

That having been said, in this type of scenario there is usually some kind
of flag on the sheet that denotes a date or something that can be used to
calculate how many date periods and hence data points should be analysed,
even if it's just using something like MATCH/HLOOKUP etc to marry up the
current month and year with the dates that i assume sit on top of this data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Aladin Akyurek" wrote in message
...
Ken,

Maybe...

=AVERAGE(OFFSET(G34,,MATCH(2,1/(G34:Z34<0))-1,,-5))

is what Geo is looking for.

The formula must be confirmed with control+shift+enter.

Note also that the formula does not check whether there is a sufficent
amount of data points in the range of interest.

<snip

Hi,
First off I would like to apologize for wasting peoples time. That is that
last thing that I want to do. I feel that this is a great thing going on and
that the more knowledgable people are providing a great service to people
like me. So again I apologize.
Now that I have that off my chest.....What I am donig is a handicap
worksheet. Yes some of the cells in the rows could possibly have zeros in
them (But my score won't reflect that). I didn't want to use them if the
column didn't have data in them (Zeros in the row after my last column of
data). So far everything offer to me as far as help has been fantastic, and I
think that I am in good shape. I am probably asking the worksheet to do to
much. I think that I am trying to get to complex with this and should stick
with simplicity. Thank you so much for everyones time.

Also, I think that the last formula give by Aladin did the trick for me,
Thanks!!


"Ken Wright" wrote:

Hi Aladin, given the OPs last reply to me

Yes, There is a possibility that a number in the 34 row may be less than
zero
in the future.

I'd now be dubious about relying on anything other than manually telling it
how many data points to cater for. If the value can be greater than 0 and
less than 0 then I have to believe it is possible to actually be 0. If he's
100% sure it can't then that should do him though :-)

That having been said, in this type of scenario there is usually some kind
of flag on the sheet that denotes a date or something that can be used to
calculate how many date periods and hence data points should be analysed,
even if it's just using something like MATCH/HLOOKUP etc to marry up the
current month and year with the dates that i assume sit on top of this data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Aladin Akyurek" wrote in message
...
Ken,

Maybe...

=AVERAGE(OFFSET(G34,,MATCH(2,1/(G34:Z34<0))-1,,-5))

is what Geo is looking for.

The formula must be confirmed with control+shift+enter.

Note also that the formula does not check whether there is a sufficent
amount of data points in the range of interest.

<snip

LOL - If that's how it came across then my apologies as it wasn't meant to.
If Aladin's solution works then good stuff and go with it. Almost anything
can be catered for, but we have to know the rules of engagement with the
data :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
Hi,
First off I would like to apologize for wasting peoples time. That is that
last thing that I want to do. I feel that this is a great thing going on
and
that the more knowledgable people are providing a great service to people
like me. So again I apologize.
Now that I have that off my chest.....What I am donig is a handicap
worksheet. Yes some of the cells in the rows could possibly have zeros in
them (But my score won't reflect that). I didn't want to use them if the
column didn't have data in them (Zeros in the row after my last column of
data). So far everything offer to me as far as help has been fantastic,
and I
think that I am in good shape. I am probably asking the worksheet to do
to
much. I think that I am trying to get to complex with this and should
stick
with simplicity. Thank you so much for everyones time.

Also, I think that the last formula give by Aladin did the trick for me,
Thanks!!


"Ken Wright" wrote:

Hi Aladin, given the OPs last reply to me

Yes, There is a possibility that a number in the 34 row may be less
than
zero
in the future.

I'd now be dubious about relying on anything other than manually telling
it
how many data points to cater for. If the value can be greater than 0
and
less than 0 then I have to believe it is possible to actually be 0. If
he's
100% sure it can't then that should do him though :-)

That having been said, in this type of scenario there is usually some
kind
of flag on the sheet that denotes a date or something that can be used
to
calculate how many date periods and hence data points should be
analysed,
even if it's just using something like MATCH/HLOOKUP etc to marry up the
current month and year with the dates that i assume sit on top of this
data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Aladin Akyurek" wrote in message
...
Ken,

Maybe...

=AVERAGE(OFFSET(G34,,MATCH(2,1/(G34:Z34<0))-1,,-5))

is what Geo is looking for.

The formula must be confirmed with control+shift+enter.

Note also that the formula does not check whether there is a sufficent
amount of data points in the range of interest.

<snip

Hi,
Nothing you or Aladin said came across poorly. A comment that was said in a
different post made me feel that I had somehow abused the discussion group
which was not my intention. Im sure that Ill need more help at a later
time. Thanks again!

€œJust to make sure he posted again a third time and wasted Ken Wright's time
as well as

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


"Ken Wright" wrote:

LOL - If that's how it came across then my apologies as it wasn't meant to.
If Aladin's solution works then good stuff and go with it. Almost anything
can be catered for, but we have to know the rules of engagement with the
data :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Geo" wrote in message
...
Hi,
First off I would like to apologize for wasting peoples time. That is that
last thing that I want to do. I feel that this is a great thing going on
and
that the more knowledgable people are providing a great service to people
like me. So again I apologize.
Now that I have that off my chest.....What I am donig is a handicap
worksheet. Yes some of the cells in the rows could possibly have zeros in
them (But my score won't reflect that). I didn't want to use them if the
column didn't have data in them (Zeros in the row after my last column of
data). So far everything offer to me as far as help has been fantastic,
and I
think that I am in good shape. I am probably asking the worksheet to do
to
much. I think that I am trying to get to complex with this and should
stick
with simplicity. Thank you so much for everyones time.

Also, I think that the last formula give by Aladin did the trick for me,
Thanks!!


"Ken Wright" wrote:

Hi Aladin, given the OPs last reply to me

Yes, There is a possibility that a number in the 34 row may be less
than
zero
in the future.

I'd now be dubious about relying on anything other than manually telling
it
how many data points to cater for. If the value can be greater than 0
and
less than 0 then I have to believe it is possible to actually be 0. If
he's
100% sure it can't then that should do him though :-)

That having been said, in this type of scenario there is usually some
kind
of flag on the sheet that denotes a date or something that can be used
to
calculate how many date periods and hence data points should be
analysed,
even if it's just using something like MATCH/HLOOKUP etc to marry up the
current month and year with the dates that i assume sit on top of this
data.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

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

"Aladin Akyurek" wrote in message
...
Ken,

Maybe...

=AVERAGE(OFFSET(G34,,MATCH(2,1/(G34:Z34<0))-1,,-5))

is what Geo is looking for.

The formula must be confirmed with control+shift+enter.

Note also that the formula does not check whether there is a sufficent
amount of data points in the range of interest.

<snip