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

[Dave Hawley]

Hi Barney


Rather confuse things with UDF or a deeply nested mega formula, I would suggest creating a Dynamic range that will only include the last 10 scores. In this example the scores are in Column "B".

1. In column "A" starting at A1 put this formula: =COUNTA(B1:$B$40) Now copy this down to A40

2. Now go to Insert>Name>Define and type: LastTen in the "Names in Workbook:" box

3. In the "Refers to" box copy and paste this:
=INDIRECT(ADDRESS(MATCH(10,Sheet1!$A$1:$A$40,0),2) & ":" & "$B$40")

4. Click "Add" then "OK"


Now you can hide Column "A" if you wish and simply use:

=Average("LastTen")


To be even more flexible you could substitute the number "10" in the MATCH function for a cell Reference, eg $C$10 then you could simply type the number $C$10 that you want to average of.





_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training




[ This Message was edited by: Dave Hawley on 2002-03-31 23:44 ]

[bstoutam]

Dave,
Thank you very much for taking the time to assist me with my Excel problem. It is very generous and kind of you. However, so far I have been unable to make it work, probably due to my lack of knowledge.
I was hoping to have a "running average" of the last ten scores in column "C", e.g., with golf scores in the column "B". Each time a golf score is added to column "B", the average in the column "C" should update by dropping the 11th score up and including the new score. (The complication is that the last ten scores are not necessarily the last ten rows/cells, since there may be one or more blank cells in the last ten rows.) The scores and the averages should show up the column for 40 or more rows/cells.
Thanks again,
Barney

[Dave Patton]

The following Array Formula will calculate the average of the last 10 scores ignoring blanks.

Enter the formula with Ctrl-Shift-Enter (CSE).

=AVERAGE(A50:INDEX(A:A,LARGE(IF(A1:A50<>"",ROW(A1:A50)),10)))

Revise the references as necessary.

Instead of hardcoding the 10, you can use a cell references such as B1. If you want an average of the the last 2 (you may have just played 3 games) put 2 in B1.

=AVERAGE(A50:INDEX(A:A,LARGE(IF(A1:A50<>"",ROW(A1:A50)),B1)))



[ This Message was edited by: Dave Patton on 2002-04-01 21:29 ]

[bstoutam]

Dave P.
Thank you very much for your help. Your formula works great, but since it only shows one number representing the average of the most recent last ten scores, I am unable to see the history or change in the averages. I was hoping to see the series/history of averages in a column adjacent to the "scores" column.
You have been very generous with your time/expertise.
Thank you again,
Barney Stoutamire

[Dave Patton]

To show a rolling average, consider the following; however, if there is a blank the average is probably of the last 9.

- numbers in Col B starting at B10
- formula for early month

=AVERAGE($B$10:B10) and copy down

When 10 numbers are available, averaging the last 10 is done with

=AVERAGE(B10:B19)
and when you copy this down it becomes

=AVERAGE(B11:B20) etc.

NOTE. Scores starting at B10. Put the revised formual in an adjacent column, Array Enter it and copy it down.
=AVERAGE(B10:INDEX(B:B,LARGE(IF($B$10:B10<>"",ROW($A$10:A10)),ROW()-9)))

When 10 rows are completed revise the formula to 10 or reference B2

=AVERAGE(B20:INDEX(B:B,LARGE(IF(B10:B20<>"",ROW(A10:A20)),$B$2))) CSE




[ This Message was edited by: Dave Patton on 2002-04-02 20:35 ]

[ This Message was edited by: Dave Patton on 2002-04-02 20:53 ]

[bstoutam]

I am using that formula now but, of course it can't deal with blank cells. Is there any way to do what you describe and have the formula ignore blank cells; that is have a rolling average of the last ten scores even if a person did't play on a particular date?
Thanks,
Barney

[Dave Patton]

Barney see edited suggestion shown above.

[ This Message was edited by: Dave Patton on 2002-04-02 20:56 ]

[bstoutam]

Thank you again, Dave. The array formula averages the last ten scores as long as there are no blank cells. When there are blank cells the message, "#num!" appears. The formula should be able to ignore as many as 12 blank cells, then when a score is entered, use that score, ignore 1-12 blank ones and include 9 other scores in the average it posts by the last score.
Barney

[Dave Patton]

Try

=IF(COUNT($B$10:B10)<10,AVERAGE($B$10:B10),AVERAGE(B10:INDEX(B:B,LARGE(IF($B$10:B10<>"",ROW($A$10:A10)),10))))

Array enter and copy down.



[ This Message was edited by: Dave Patton on 2002-04-03 06:39 ]