+ Reply to Thread
Results 1 to 8 of 8

Averaging numbers at intervals

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    Auburn, WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Averaging numbers at intervals

    I'm trying to get an average of cells that will change at intervals. For example, X 381 289 546 295 402 396 where X is the average and changes only at the 3rd cell, then the 6th, etc.

    Here's what I'm trying to fix. Book2.xlsx

    Thanks in advance,

    Greg
    Last edited by RPGreg; 06-04-2013 at 02:38 PM.

  2. #2
    Registered User
    Join Date
    06-04-2013
    Location
    Auburn, WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Averaging numbers at intervals

    Still hoping for a solution.

    Greg

  3. #3
    Registered User
    Join Date
    06-05-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    90

    Re: Averaging numbers at intervals

    So you want the data in your M column to update every 3rd entry in your "data"? so starting on R or P?

  4. #4
    Registered User
    Join Date
    06-04-2013
    Location
    Auburn, WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Averaging numbers at intervals

    P would be the initial average, then every third after that. R, then U, then X, etc. The first is generated to give players their initial rating, but we only want the players to be re-rated after every 3 matches that they play.

    Greg
    Last edited by RPGreg; 06-05-2013 at 06:35 PM.

  5. #5
    Registered User
    Join Date
    06-04-2013
    Location
    Auburn, WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Averaging numbers at intervals

    Just bumping this up to the top.

    Greg

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Averaging numbers at intervals

    Some amount of top-level explanation about what all this means would probably be helpful. It's a sea of colors, numbers, and ratings.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Averaging numbers at intervals

    if you add your solutions in your file (manual) then i will take a look if I can be of any help.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  8. #8
    Registered User
    Join Date
    06-04-2013
    Location
    Auburn, WA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Averaging numbers at intervals

    The M column (Average) is the important field. Fields C - K are simply manual entry for their Average score determined in another spreadsheet. Column N is determined by the following formula

    =IF(M10="","",IF(M10<250.5,1,IF(M10<270.5,2,IF(M10<290.5,3,IF(M10<310.5,4,IF(M10<330.5,5,IF(M10<350.5,6,IF(M10<370.5,7,IF(M10<390.5,8,IF(M10<410.5,9,IF(M10<430.5,10,IF(M10<450.5,11,12))))))))))))

    The O column (Games) is simply a count of the matches played.

    So, as values are added to fields P - AC, we want the M Column (Average) to be updated after every third match played. We don't want a running change in a player's rating because we want to protect against one bad game or, conversely, one really great game.

    The formula that's in the M column now is this:

    =IF(P10="","",IF(B10<>"",AVERAGE(data),IF(O10>47,AVERAGE(data),AVERAGE(P10))))

    Honestly, I'm not sure why there's a reference to the B column.

    Greg

+ 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