return last values

R

Rob_B

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
 
B

Bob Phillips

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)
 
S

Sandy Mann

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(RightList,COUN
T(RightList)-9):INDEX(RightList,COUNT(RightList)))+SUM(INDEX(RightList,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
 
S

Sandy Mann

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=LARGE(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
 
S

Sandy Mann

Bob Phillips said:
If it works, the OP should like it :)
Click to expand...

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

Sandy
 
P

Peo Sjoblom

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

Sandy Mann

Peo Sjoblom said:
Yes, I don't know where I got the last 10 values. <g>
Must have been the subject.
Click to expand...

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
 
S

Sandy Mann

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
 
Top