I want to create a moving average, exponential, weighted or simple(my preferences in that order) of the times that individual dogs take to complete a task. The task is a timed run over a course. It would be a moving average of the time awarded to each dog for each heat it competes in for the last 50 heats that the dog competed in. There are up to 5 heats in each race and each dog may or may not run in any or all of the heats. The dogs on a team are not static. I would need the formula to search two sheets in a workbook to find the dogs and their times and to return the moving average result in a column next to the dog’s name on a separate sheet. The formula would need to ignore any text that is in any cell instead of a time, example #NA or NA.
I’ve enclosed a workbook with three sheets, two sheets contain the data for the two different teams of dogs, and the third sheet contains the names of the dogs, all of which are not yet included in the data but may be in the future. In years to come even more dogs will be added and as some retire they will be removed from the list.
I am a beginner to Excel and any help would be appreciated.
Thanks
Doug
Bookmarks