Macro to calculate moving average of dynamically updated cell

[rob51852]

Hi,

In cell B2 values are updated dynamically each half second by external software.

I would like to create a simple macro that calculates the average of the previous 10 values and inserts the result in cell C2.

Is this possible?

Thanks

 

[Yongle]

Try this in a COPY of your workbook to see if you can get it working
- for the first test simply use the same cells as I used for everything (to prove the model)
- modify the ranges to suit your preferred worksheet layout after that

B2 is the cell being updated
C2 contains the average
D2:M2 contain the last 10 values

TEST1
1. Enter Formula below in B1 (this is used to force an event trigger - which may be unnecessary)
=NOW()

2. Place this code in the SHEET code window
- right click on sheet tab\ select view code \ paste the code into that window

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Range("D2").Resize(, 9).Copy Range("E2")
    Range("B2").Copy Range("D2")
    Range("C2").Formula = "=AVERAGE(D2:M2)"
Debug.Print Time, Range("B2")
    Application.EnableEvents = True
End Sub

3. Test to see if it works

Notes to help you identify any issues etc
Debug.Print writes a value to the Immediate Window in VBA Editor EVERY time the code is triggered
- after testing that line can be removed
- it will help you see how often the event is triggered when B2 is updated
- display the immediate window with {CTRL} g when in VBA editor

The code above is triggered every time any cell is amended in the worksheet
- but the value in B2 may not have changed when the cell is amended
- to recalculate the average only when the value in B2 changes then ...

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If Range("B2") <> Range("D2") Then
        Range("D2").Resize(, 9).Copy Range("E2")
        Range("B2").Copy Range("D2")
        Range("C2").Formula = "=AVERAGE(D2:M2)"
    End If
Debug.Print Time, Range("B2")
    Application.EnableEvents = True
End Sub

Let me know how you get on
.
.

 

[rob51852]

Thanks Yongle. I’ll try it out.