Rolling Average

Hi, Hello, and Welcome to MrExcel --

Are we going to have all these samples in one column? How are they input?

Hello Just_Jon, thank you for the quick response. The samples will be all in the same column. The info is a .dbf file which is logged in RSView32 software. When the month of logging is complete the .dbf file is viewed through excel. The temperature will be in D2 through D21,601. I would need the rolling average calculated and displayed in E2 through E21,601. The site Excel experts told me it can't be done but I don't believe that. I have a little VB training but I've never used it in Excel. I appreciate any help you could give.

Thanks,
MIke

So, each row will have an average on it, even if it is the of the first few minutes/hours.

Will the date and time also be present? If so, where.

When you say every 2 minutes, will this be exactly? And a 3-hr average, if you've temps for all day will the 3PM average include or exclude noon?

This untested code should generate a simple average based on the latest data up to a max of 90 last temps [every 2 minutes = 30/hr x 3 hrs = 90 observations].

Please test it on a 100 point sample to make sure it works.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TakinMyTempWithoutAThermometer()

<SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
LastRow = [D65536].End(xlUp).Row
<SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LastRow
    <SPAN style="color:#00007F">If</SPAN> i <= 91 <SPAN style="color:#00007F">Then</SPAN>
        Cells(i, 5) = Application.WorksheetFunction.Sum(Range("D2:D" & i))
    <SPAN style="color:#00007F">Else</SPAN>
        Cells(i, 5) = Application.WorksheetFunction.Sum(Range("D" & i - 89 & ":D" & i))
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    Cells(i, 5) = Cells(i, 5) / Application.WorksheetFunction.Min(90, i - 1)
<SPAN style="color:#00007F">Next</SPAN> i

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

BTW, the above can be translated into a worksheet change event macro, which can be set to fire any time there is a change to column D, thereby deleting the need to explicitly run the macro.

Hello Just_Jon, your code work with very well for what I needed. My boss changed the format of the sheet on me but I was able to wiggle things around and use your code. Thanks for your help.

Thanks,
Mike ray:

Ned any more help, let us know.