# Need help with a formula

#### tafkap777

##### New Member
I know the basic formulas for Excel, but I need help accomplishing a certain task. I'm not even sure a formula exists for this though.

Basically, I have a spreadsheet where I add in a new column of numbers every day. I have been keeping track of the average for each row, but now am looking to keep track of the average for only the most recent 10 columns.

Is there a way for a formula to start with the right-most column (which changes every day) and then look back from there to find the average for the 10 most recent columns without me having to manually change the range every day? Does that even make sense to anyone except me?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### mumps

##### Well-known Member
I don't know about a formula but you may want to try this macro:
Code:
``````Sub FindAverage()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim lCol As Long
lCol = ActiveSheet.UsedRange.Columns.Count
Dim x As Long
For x = 2 To LastRow
Cells(x, lCol + 1) = WorksheetFunction.Average(Range(Cells(x, lCol - 9), Cells(x, lCol)))
Next x
Application.ScreenUpdating = True
End Sub``````
If you decide to use this macro, the average will be placed in the first unused column. To prevent the current average from being used in the calculation the next time you add another column of data, overwrite the current average with you new daily data and then run the macro or you could delete the column with the averages and then add the new column of data.

Last edited:

#### tafkap777

##### New Member
I have never worked with macros before, so I'm lost I tried running the macro, but it keeps giving me an error. Are their tweaks I need to make to the macro in order for it to work? For example my columns start at Y and go through BA (for today anyway).

#### mumps

##### Well-known Member
Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. I tried the macro on some dummy data and it worked properly but your actual data may be organized differently. If it is still not working for you, perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

#### István Hirsch

##### Well-known Member
Give this a try:

Put the formula below in cell A1, the numbers are in B1, C1, D1 etc. No blank spaces are permitted between the values and the cells between the rightest value and cell AAA1 should be blank. New values always go to the next column. AAA1 can be extended to the right if needed.

 * A B C D E F G H I J K L M N O 1 5.3 3 8 3 22 11 5 3 7 4 8 1 9 3 2

<colgroup><col style="width:30px; "><col style="width:81px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"><col style="width:45px;"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:

#### tafkap777

##### New Member
I'm getting an error message when I hit F5 to run the macro, "Run-time error '1004': Unable to get the Average property of the WorksheetFunction class"

#### mumps

##### Well-known Member
When I tried the macro on some dummy data it worked properly. Your data may be organized differently. It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

#### tafkap777

##### New Member
Thank you for your continued help with this, and your patience with walking me through this!

Nothing in here is private...or important. I'm just trying to manage my football league the best I can.

Here's the rundown. I have the capacity for 32 members in my league, so I am really concerned with rows 2-33. The scores for each daily game begin in column X. I have been keeping track of everyone's average score since X (column C), but now am looking to only average their last (most recent) 10 games. Does that make sense?

#### mumps

##### Well-known Member
This macro will put the average in column C. I noticed that you had a date already entered in cell BC1 even though there was no data in that column. Please delete the date in BC1 and always leave the cell in row 1 blank until you are ready to enter data in that column. Try:
Code:
``````Sub FindAverage()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Range("A2:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row - 1
Dim lColumn As Long
lColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Dim x As Long
For x = 2 To LastRow
Cells(x, 3) = WorksheetFunction.Average(Range(Cells(x, lColumn - 9), Cells(x, lColumn)))
Next x
Application.ScreenUpdating = True
End Sub``````

#### tafkap777

##### New Member
Oh my goodness, you're amazing! I tried it ten way to Tuesday and it works every time. I was even able to assign it to a button on my ribbon and it works flawlessly. Thank you so much for your patience and expertise!

Replies
2
Views
722
Replies
1
Views
103
Replies
3
Views
500
Replies
13
Views
625
Replies
3
Views
1K

1,191,177
Messages
5,985,139
Members
439,941
Latest member
robertv13

### 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.

### Which adblocker are you using?

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

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