Excel formula

[bstoutam]

I need a formula to figure a moving average for the last 10 golf scores in a column that may have 40 scores with several blank cells.
I keep a record of the "date played", "the scores", and a "moving average" (last 10 scores) for 14 golfers.

Thank you very much for any help.
Barney Stoutamire

 

[bstoutam]

Hi again Dave,
I put numbers in column B1:B32, with blank cells at B12,B18,B19, then put the formula into C10 and copied down to B32. (see numbers below) The formula looks as though it is working, in that the same average is repeated where there are blank cells; the problem is the averages are not correct.
Thanks again, Barney

20
23
18
21
24
22
23
23
27
29 29.00
27 28.00
28.00
21 25.67
25 25.50
19 24.20
29 25.00
26 25.14
25.14
25.14
21 24.63
24 24.56
14 23.50
26 23.73
27 24.00
22 23.85

 

[Dave Patton]

What formula did you use and did you correct the cell references ?

The formula was written with data starting in Row 10.

You can :
a) put the formula in your first row, edit the cell references and copy it down.

or
b) if you do NOT want to edit the formula, copy your test data and paste it starting at B10.

You can use the area at the top for other statistics.

The formula gives correct answers on my test data and on the data you presented.
This message was edited by Dave Patton on 2002-04-03 12:57

 

[bstoutam]

Dave,
You were right I did something to the formula that made it average incorrectly. In any case I now have it in my spreadsheet where I keep a modified handicap for 14 golfers. It works beautifully, precisely the way I wanted it to work.
I can't say "thank you" enough for being so generous with your time and expertise. You have been very very helpful and very patient with this Excel novice and I am most appreciative.
Barney

 

[Dexter]

Here is a solution to your dilemma. Not the most elegant, but it works.

First, set up a worksheet with two tables: Table 1 will contain the data entry (scores); Table 2 will contain a rolling average calculation.

Set up a second worksheet that contains lookup formulas that refer to the scores in Table 1. Display as many as you choose. Then setup a column to display the rolling average. The cells in this column will refer to the Table 2 in the other worksheet.