Creating a rolling average

Hi all,

First time poster, I have done quite a lot of searching trying to work out how to do this. Have found a lot of examples but not one that quite works for what I need it to do.

In sheet 1 (TEAM SHEETS), I want to create a 3 game rolling average (D6) for the data in sheet 2, D5, F5, and H5. And as the data is entered for J5 I receive a new 3 game average rolling average for F5, H5 and J5 in that same cell.

For example, the corresponding data in D5, F5, and H5 should return a 3 game rolling average of 4.3 in Sheet 1 D6. As the next value is entered into J5, Sheet 1 D6 will then change to represent the new 3 game average of 6.16. And so forth until the 82nd game at the end of the worksheet.

I would also like to do this same process for a 5 game average in Sheet 1 E6.

I hope that makes sense, if any one could help me out that would be great!

I have attached the relevant spreadsheet.

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

I should rearange the data in a table (rows instead of columns).

After that you can easily analyse the data with a pivot table.

I showed this for game 1 - 5 for ATLANTA HAWKS, just to give you an idea.

See the attached file.

That is the way I should go.

Maybe other forummembers has other idea's for you.

New idea.

Maybe you can make a formular with dropdownboxes to fill the data.

Probably this will increase the inputtime.

Is this the same query as the other thread on rolling averages? If so, then you should continue there.

I will delete the old thread, I have constructed a new table to manipulate the data.

Threads merged.

Well done!!!

When you copy and paste, any cell reference that is not fixed will change based on its new relative location.