Excel formula

bstoutam

New Member
Joined
Mar 30, 2002
Messages
7
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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
OzGrid.BusApp.170x45.gif

This message was edited by Dave Hawley on 2002-03-31 23:44
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Barney see edited suggestion shown above.
This message was edited by Dave Patton on 2002-04-02 20:56
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top