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