Dave/Aladdin Help! Impossible Puzzler!
Posted by Ricardo Bunge on March 30, 2001 4:30 PM
Am in dire need of assistance... need this solution by Monday (if possible)... will try to summarize: How to average last 25 numbers in rows -- caveats: must be rolling 25 (but not a fixed 25 b/c of blanks), ignore blanks, and allow for adding of columns as each new series is entered. Names along left, numbers across. Not everyone gets an entry each time, so possible for someone to have, say 3 scores, then nothing for 10 cells. Have to average the most recent 25 scores, but cannot just use OFFSET for 25 cells b/c some or most within that 25 might be blank and would thus have to take earlier ones (say, 37 cells back)... sounds complicated, I know, but hope you understand. 3 people have spent nearly two days on this, tried nested IFs, OFFSETS, etc, but nothing satisfies the requirement. ANY assistance would be MOST appreciated. Please advise if require clarification. Thanks!!!
Posted by Jacob Hilderbrand on March 30, 2001 8:03 PM
This will do the trick. Hope it helps.
Sub test()
'Lets say the data is in column A. You can change
'this to your needs.
Dim Last_Row
Dim My_Total
Dim My_Count
Application.ScreenUpdating = False
Range("A65536").Select
Selection.End(xlUp).Select
Last_Row = ActiveCell.Row
My_Total = 0
My_Count = 0
a:
Range("A" & Last_Row).Select
If ActiveCell.Value > 0 Then
My_Count = My_Count + 1
My_Total = My_Total + ActiveCell.Value
End If
If My_Count = 25 Then GoTo z:
Last_Row = Last_Row - 1
If Last_Row = 0 Then GoTo z:
GoTo a:
z:
MsgBox ("The Average Is: " & My_Total / My_Count), vbInformation, "Average"
End Sub
Jacob
Posted by Dave Hawley on March 30, 2001 9:57 PM
Hi Ricardo
Let's see if I have understood you.
For this example I will assume the Column you wish to average the last 25 numeric in, is column B. It also assumes you have a heading in Column B.
1. In cell D1 type: Very Last Cell
2. In cell D2 put: =ADDRESS(MATCH(-1E+25,B:B,-1),2)
3. In Cell E1 type: Last cell with numeric entries below it
4. In cell E2 put: =ADDRESS(ROW(INDIRECT($D$2))-25-COUNTBLANK(INDIRECT(ADDRESS(ROW(INDIRECT($D$2))-25,2)&":"&$D$2))+1,2)
5. In cell F1 type: Average of last 25 entries
6. In cell F2 put: =AVERAGE(INDIRECT($D$2&":"&$E$2))
This will average the last 25 cells that contain numeric entries.
Dave
OzGrid Business Applications
