Need help with a formula

tafkap777

New Member
Joined
Dec 19, 2016
Messages
13
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? :confused:
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:
Upvote 0
I have never worked with macros before, so I'm lost :eek: 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).
 
Upvote 0
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.
 
Upvote 0
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.

*ABCDEFGHIJKLMNO
15.33832211537481932

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

Spreadsheet Formulas
CellFormula
A1=SUM(INDIRECT(ADDRESS(1,COUNTA($B1:AAA1)-8)):INDIRECT(ADDRESS(1,COUNTA($B1:AAA1)+1)))/10

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
Thank you for your continued help with this, and your patience with walking me through this!

Here is the link to my document https://www.dropbox.com/s/r11w7p4ijenef1z/MaddenMobile.xlsx?dl=0

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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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