+ Reply to Thread
Results 1 to 10 of 10

return last values

  1. #1
    Rob_B
    Guest

    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

  2. #2
    Peo Sjoblom
    Guest

    Re: return last values

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  3. #3
    Bob Phillips
    Guest

    Re: return last values

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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




  4. #4
    Sandy Mann
    Guest

    Re: return last values

    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
    --
    to e-mail direct replace @mailinator.com with @tiscali.co.uk


    "Peo Sjoblom" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >




  5. #5
    Sandy Mann
    Guest

    Re: return last values

    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
    --
    to e-mail direct replace @mailinator.com with @tiscali.co.uk


    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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

    >
    >




  6. #6
    Bob Phillips
    Guest

    Re: return last values

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

    Regards

    Bob


    "Sandy Mann" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > --
    > to e-mail direct replace @mailinator.com with @tiscali.co.uk
    >
    >
    > "Bob Phillips" <[email protected]> wrote in message
    > news:%[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >

    >
    >




  7. #7
    Sandy Mann
    Guest

    Re: return last values

    "Bob Phillips" <[email protected]> wrote in message
    news:%[email protected]...
    > 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




  8. #8
    Peo Sjoblom
    Guest

    Re: return last values

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

    --

    Regards,

    Peo Sjoblom

    "Sandy Mann" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > --
    > to e-mail direct replace @mailinator.com with @tiscali.co.uk
    >
    >
    > "Peo Sjoblom" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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

    > >
    > >

    >
    >




  9. #9
    Sandy Mann
    Guest

    Re: return last values

    "Peo Sjoblom" <[email protected]> wrote in message
    news:uDkS10%[email protected]...
    > 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




  10. #10
    Sandy Mann
    Guest

    Re: return last values

    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" <[email protected]> wrote in message
    news:[email protected]...
    > "Peo Sjoblom" <[email protected]> wrote in message
    > news:uDkS10%[email protected]...
    > > 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
    >
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1