Averaging numbers at intervals

**RPGreg** · 06-04-2013, 02:15 PM

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

**RPGreg** · 06-05-2013, 12:50 PM

Still hoping for a solution.

Greg

**bcas77** · 06-05-2013, 02:17 PM

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

**RPGreg** · 06-05-2013, 04:01 PM

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

**RPGreg** · 06-06-2013, 12:28 PM

Just bumping this up to the top.

Greg

**shg** · 06-06-2013, 12:35 PM

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

**oeldere** · 06-06-2013, 12:36 PM

if you add your solutions in your file (manual) then i will take a look if I can be of any help.

**RPGreg** · 06-06-2013, 02:30 PM

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