return last values
 

Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores, but for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

Use a named dynamic range


http://www.contextures.com/xlNames01.html#Dynamic


assyume it's named MyList

=AVERAGE(INDEX(MyList,COUNT(MyList)-9):INDEX(MyList,COUNT(MyList)))

or

=AVERAGE(OFFSET(MyList,COUNT(MyList)-1,,-10,))

the first is better since it's not volatile





--

Regards,

Peo Sjoblom


"Rob_B" wrote in message
...
Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores, but
for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

Rob,

Assuming dates in A1:A20, scores in B1:B20, the last 10 scores can be
obtained with

=AVERAGE(IF(A1:A20=LARGE(A1:A20,{1,2,3,4,5,6,7,8,9 ,10}),B1:B20))

--

HTH

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


"Rob_B" wrote in message
...
Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores, but
for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

Peo,

I may be reading the OP wrong - it wouldn't be the first time - but does you
formula weight the last six games (three dates) as double value?

Making the named range "MyList" the *Game1Score* list and "RightList" the
*Game2Score* list and adapting your formula:

=(SUM(INDEX(MyList,COUNT(MyList)-9):INDEX(MyList,COUNT(MyList)))+SUM(INDEX(M
yList,COUNT(MyList)-2):INDEX(MyList,COUNT(MyList)))+SUM(INDEX(RightLis t,COUN
T(RightList)-9):INDEX(RightList,COUNT(RightList)))+SUM(INDEX(Ri ghtList,COUNT
(RightList)-2):INDEX(RightList,COUNT(RightList))))/10

does what I think the OP wanted namely averaging the last 10 sets of two
games with the last three game scores doubled.

Regards

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
Use a named dynamic range


http://www.contextures.com/xlNames01.html#Dynamic


assyume it's named MyList

=AVERAGE(INDEX(MyList,COUNT(MyList)-9):INDEX(MyList,COUNT(MyList)))

or

=AVERAGE(OFFSET(MyList,COUNT(MyList)-1,,-10,))

the first is better since it's not volatile





--

Regards,

Peo Sjoblom


"Rob_B" wrote in message
...
Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores, but
for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I
calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

Bob,

As I said to Peo, I may be wrong but the way that I read it, your formula
does not do what the OP wants, namely average the last 10 sets of two games
with the final 3 sets of two game score doubled.

Adapting your formula, I think that the array formula:

=(SUM(IF(A1:A31=LARGE(A1:A31,{1,2,3,4,5,6,7,8,9,10 }),B1:B31,0))+SUM(IF(A1:A3
1=LARGE(A1:A31,{1,2,3}),B1:B31,0))+SUM(IF(A1:A31=L ARGE(A1:A31,{1,2,3,4,5,6,7
,8,9,10}),C1:C31,0))+SUM(IF(A1:A31=LARGE(A1:A31,{1 ,2,3}),C1:C31,0)))/10

does what the OP wants but I can't say that I like it.

Regards

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" wrote in message
...
Rob,

Assuming dates in A1:A20, scores in B1:B20, the last 10 scores can be
obtained with

=AVERAGE(IF(A1:A20=LARGE(A1:A20,{1,2,3,4,5,6,7,8,9 ,10}),B1:B20))

--

HTH

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


"Rob_B" wrote in message
...
Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores, but
for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I
calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

If it works, the OP should like it :-)

Regards

Bob


"Sandy Mann" wrote in message
...
Bob,

As I said to Peo, I may be wrong but the way that I read it, your formula
does not do what the OP wants, namely average the last 10 sets of two
games
with the final 3 sets of two game score doubled.

Adapting your formula, I think that the array formula:


=(SUM(IF(A1:A31=LARGE(A1:A31,{1,2,3,4,5,6,7,8,9,10 }),B1:B31,0))+SUM(IF(A1:A3

1=LARGE(A1:A31,{1,2,3}),B1:B31,0))+SUM(IF(A1:A31=L ARGE(A1:A31,{1,2,3,4,5,6,7
,8,9,10}),C1:C31,0))+SUM(IF(A1:A31=LARGE(A1:A31,{1 ,2,3}),C1:C31,0)))/10

does what the OP wants but I can't say that I like it.

Regards

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Bob Phillips" wrote in message
...
Rob,

Assuming dates in A1:A20, scores in B1:B20, the last 10 scores can be
obtained with

=AVERAGE(IF(A1:A20=LARGE(A1:A20,{1,2,3,4,5,6,7,8,9 ,10}),B1:B20))

--

HTH

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


"Rob_B" wrote in message
...
Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores,
but
for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I
calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

"Bob Phillips" wrote in message
...
If it works, the OP should like it :-)

Like when I was learning to fly - if you can walk away from it, it's a good
landing <g

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

Yes, I don't know where I got the last 10 values. <g
Must have been the subject.

--

Regards,

Peo Sjoblom

"Sandy Mann" wrote in message
...
Peo,

I may be reading the OP wrong - it wouldn't be the first time - but does
you
formula weight the last six games (three dates) as double value?

Making the named range "MyList" the *Game1Score* list and "RightList" the
*Game2Score* list and adapting your formula:


=(SUM(INDEX(MyList,COUNT(MyList)-9):INDEX(MyList,COUNT(MyList)))+SUM(INDEX(M

yList,COUNT(MyList)-2):INDEX(MyList,COUNT(MyList)))+SUM(INDEX(RightLis t,COUN

T(RightList)-9):INDEX(RightList,COUNT(RightList)))+SUM(INDEX(Ri ghtList,COUNT
(RightList)-2):INDEX(RightList,COUNT(RightList))))/10

does what I think the OP wanted namely averaging the last 10 sets of two
games with the last three game scores doubled.

Regards

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
Use a named dynamic range


http://www.contextures.com/xlNames01.html#Dynamic


assyume it's named MyList

=AVERAGE(INDEX(MyList,COUNT(MyList)-9):INDEX(MyList,COUNT(MyList)))

or

=AVERAGE(OFFSET(MyList,COUNT(MyList)-1,,-10,))

the first is better since it's not volatile





--

Regards,

Peo Sjoblom


"Rob_B" wrote in message
...
Hi, I have a bowling spreadsheet that I list as:
Date Game1score Game2score

I want to be able to use a weighted average by averaging all scores,
but
for
the last 3 dates (6 games), weight them double.

Also, this will be a continually growing spreadsheet. How to I
calculate
averages so that it is only looking at the past 10 dates that it is
averaging. I do not want to average the entire sheet.

Thanks for any help.
Rob

"Peo Sjoblom" wrote in message
...
Yes, I don't know where I got the last 10 values. <g
Must have been the subject.


I was even worse than you. If I had followed the link that you gave the OP
I would have seen that you can have a dynamic named range of the last 10
rows and another of the last three rows, both of them covering both columns.
All that is required then is the formula:

=AVERAGE(SUM(MyList)+SUM(MyList2))

My apologies for sounding off like an idiot.

Regards

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk

In my embarrassment I was too egar to post and posted rubbish. AVERAGE
cannot average anything like that.

=SUM(MyList,MyList2)/20

should do it.

If you want me I'll be over in the corner <g

Regards

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
"Peo Sjoblom" wrote in message
...
Yes, I don't know where I got the last 10 values. <g
Must have been the subject.


I was even worse than you. If I had followed the link that you gave the
OP
I would have seen that you can have a dynamic named range of the last 10
rows and another of the last three rows, both of them covering both
columns.
All that is required then is the formula:

=AVERAGE(SUM(MyList)+SUM(MyList2))

My apologies for sounding off like an idiot.

Regards

Sandy
--
to e-mail direct replace @mailinator.com with @tiscali.co.uk