"Average" questions
 

I need help. (Obviously)

How can I find simple (and weighted) averages of a column of numbers...
but not the entire column. Example, I have a column of 50 data points
and want to average the last 8 numbers only. The trick here is that my
data set is 50 columns wide as well, but each successive column gets
one number less. So Column A has 50, from row 1 to 50, Column b has row
1 to 49 etc. all the way to one row only out at column 50. I want to
average the last 8 numbers only and then drag my formula across all
columns and not have to go into the formula bar and change my array by
one row for each column.

Does this make sense? If anyone is an actuary I am working on insurance
loss development triangles if that helps. I am trying to make getting my
LDF picks easier.

Thanks,

Jason


--
Bootroots
------------------------------------------------------------------------
Bootroots's Profile: http://www.excelforum.com/member.php...o&userid=36126
View this thread: http://www.excelforum.com/showthread...hreadid=558989

"Average" questions
 

Bootroots Wrote:
I need help. (Obviously)

How can I find simple (and weighted) averages of a column of numbers...
but not the entire column. Example, I have a column of 50 data points
and want to average the last 8 numbers only. The trick here is that my
data set is 50 columns wide as well, but each successive column gets
one number less. So Column A has 50, from row 1 to 50, Column b has row
1 to 49 etc. all the way to one row only out at column 50. I want to
average the last 8 numbers only and then drag my formula across all
columns and not have to go into the formula bar and change my array by
one row for each column.

Does this make sense? If anyone is an actuary I am working on insurance
loss development triangles if that helps. I am trying to make getting my
LDF picks easier.

Thanks,

Jason

Try this and see if it helps you.

*=average(offset($A$1,count(A1:A50)-8,0,8))*


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=558989

"Average" questions
 

Bootroots Wrote:
I need help. (Obviously)

How can I find simple (and weighted) averages of a column of numbers...
but not the entire column. Example, I have a column of 50 data points
and want to average the last 8 numbers only. The trick here is that my
data set is 50 columns wide as well, but each successive column gets
one number less. So Column A has 50, from row 1 to 50, Column b has row
1 to 49 etc. all the way to one row only out at column 50. I want to
average the last 8 numbers only and then drag my formula across all
columns and not have to go into the formula bar and change my array by
one row for each column.

Does this make sense? If anyone is an actuary I am working on insurance
loss development triangles if that helps. I am trying to make getting my
LDF picks easier.

Thanks,

Jason

Try this and see if it helps you.

*=average(offset($A$1,count(A1:A50)-8,0,8))*


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=558989

"Average" questions
 

Hi

One way, place the following formula in say cell A52
=SUM(INDEX(A1:A50,COUNT(A1:A50)-MIN((COUNT(A1:A50)-1),7)):INDEX(A1:A50,COUNTA(A1:A50)))/8

Copy across for all of your columns


--
Regards

Roger Govier


"Bootroots"
wrote in message
...

I need help. (Obviously)

How can I find simple (and weighted) averages of a column of
numbers...
but not the entire column. Example, I have a column of 50 data points
and want to average the last 8 numbers only. The trick here is that my
data set is 50 columns wide as well, but each successive column gets
one number less. So Column A has 50, from row 1 to 50, Column b has
row
1 to 49 etc. all the way to one row only out at column 50. I want to
average the last 8 numbers only and then drag my formula across all
columns and not have to go into the formula bar and change my array by
one row for each column.

Does this make sense? If anyone is an actuary I am working on
insurance
loss development triangles if that helps. I am trying to make getting
my
LDF picks easier.

Thanks,

Jason


--
Bootroots
------------------------------------------------------------------------
Bootroots's Profile:
http://www.excelforum.com/member.php...o&userid=36126
View this thread:
http://www.excelforum.com/showthread...hreadid=558989

"Average" questions
 

=AVERAGE(LARGE(IF(A1:A50<"",ROW(A1:A50)),ROW(INDI RECT("1:"&MIN(8,COUNT(A1:A
50))))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy across


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Bootroots" wrote
in message ...

I need help. (Obviously)

How can I find simple (and weighted) averages of a column of numbers...
but not the entire column. Example, I have a column of 50 data points
and want to average the last 8 numbers only. The trick here is that my
data set is 50 columns wide as well, but each successive column gets
one number less. So Column A has 50, from row 1 to 50, Column b has row
1 to 49 etc. all the way to one row only out at column 50. I want to
average the last 8 numbers only and then drag my formula across all
columns and not have to go into the formula bar and change my array by
one row for each column.

Does this make sense? If anyone is an actuary I am working on insurance
loss development triangles if that helps. I am trying to make getting my
LDF picks easier.

Thanks,

Jason


--
Bootroots
------------------------------------------------------------------------
Bootroots's Profile:
http://www.excelforum.com/member.php...o&userid=36126
View this thread: http://www.excelforum.com/showthread...hreadid=558989

"Average" questions
 

Thanks everyone! I will try all and see what works for me.:)


--
Bootroots
------------------------------------------------------------------------
Bootroots's Profile: http://www.excelforum.com/member.php...o&userid=36126
View this thread: http://www.excelforum.com/showthread...hreadid=558989