Here are a few Savitzky-Golay filters you can use to smooth things like voltage history. Savitzky-Golay filters are quite effective and are commonly used in spectroscopy work where you don't want to shift the positions of resonant peaks. They're also good for audio filtering. Lot's of info about 'em on the 'net, plus tables of coefficients if you want to have all the intermediate ones. You can also calculate longer series, but the typing gets a bit annoying and the chance of error goes up. Hopefully I got the numbers right here.
Use your vba editor in Excel and cut and paste these into a module. They should then show up in your macro list to run. I wanted them to be entirely self contained, so a somewhat inelegant three input boxes are presented. The assumption is that your input data will be in the first column and you get to choose the start and stop rows. Then you get to choose which column the output will go in. That's by number, not letter; A=1, B=2, C=3 etc.
Better vba programmers than me can probably improve on these greatly. It's a brute force method and might be faster if For Each were used. These filters are often done using convolution, which is much faster, but way above my pay grade. I didn't use a User Form for input because I wanted them self-contained- I keep a lot of macros in my template that I use for everything. I also don't know what Excel version they'll be compatible with, but probably all.
Enjoy & give some feedback on whether they work OK and if you have improvements.
Sub SG_five()
'5 Point Savitzky-Golay Smoothing Filter
'Multiple InputBoxes are used so the macro is self-contained.
'Note that this is a fixed output calculation and does not update if input data is changed.
On Error GoTo NotValidInput
Dim i As Integer 'counter
Dim Lrow As Integer 'low cell of data column
Dim Urow As Integer 'high cell of data column
Dim Ic As Integer 'column NUMBER for input data
Dim Cnum As Integer 'column NUMBER for output
Ic = InputBox("Enter column NUMBER of input data (A=1, B=2 etc.)")
Lrow = InputBox("Enter row number of first data cell.")
Urow = InputBox("Enter row number of last data cell.")
Cnum = InputBox("Enter column NUMBER for output (A=1, B=2 etc.).")
'5 point S-G coefficients are -3, 12, 17, 12, -3 and the divisor is 35
For i = (Lrow + 2) To (Urow - 2)
Cells(i, Cnum).Value = (-3 * Cells(i - 2, Ic).Value + 12 * Cells(i - 1, Ic).Value _
+ 17 * Cells(i, Ic).Value + 12 * Cells(i + 1, Ic).Value - 3 * Cells(i + 2, Ic).Value) / 35
Next i
Exit Sub
NotValidInput:
MsgBox ("Non valid entry- terminating.")
End Sub
Sub SG_eleven()
'11 Point Savitzky-Golay Smoothing Filter
'Multiple InputBoxes are used so the macro is self-contained.
'Note that this is a fixed output calculation and does not update if input data is changed.
On Error GoTo NotValidInput
Dim i As Integer 'counter
Dim Lrow As Integer 'lower cell of data column
Dim Urow As Integer 'uppper cell of data column
Dim Ic As Integer 'column NUMBER for input data
Dim Cnum As Integer 'column number for output
Ic = InputBox("Enter column NUMBER of input data (A=1, B=2 etc.)")
Lrow = InputBox("Enter row number of first data cell.")
Urow = InputBox("Enter row number of last data cell.")
Cnum = InputBox("Enter column NUMBER for output (A=1, B=2 etc.).")
For i = (Lrow + 5) To (Urow - 5)
Cells(i, Cnum).Value = (-36 * Cells(i - 5, Ic).Value + 9 * Cells(i - 4, Ic).Value + 44 * Cells(i - 3, Ic).Value + 69 * Cells(i - 2, Ic).Value _
+ 84 * Cells(i - 1, Ic).Value + 89 * Cells(i, Ic).Value + 84 * Cells(i + 1, Ic).Value + 69 * Cells(i + 2, Ic).Value _
+ 44 * Cells(i + 3, Ic).Value + 9 * Cells(i + 4, Ic).Value - 36 * Cells(i + 5, Ic).Value) / 429
Next i
Exit Sub
NotValidInput:
MsgBox ("Non valid entry- terminating.")
End Sub
Sub SG_twentynine()
'29 Point Savitzky-Golay Smoothing Filter
'Multiple InputBoxes are used so the macro is self-contained.
'Note that this is a fixed output calculation and does not update if input data is changed.
On Error GoTo NotValidInput
Dim i As Integer 'counter
Dim Lrow As Integer 'lower cell of data column
Dim Urow As Integer 'uppper cell of data column
Dim Ic As Integer 'column NUMBER for input data
Dim Cnum As Integer 'column NUMBER for output data
Ic = InputBox("Enter column NUMBER of input data (A=1, B=2 etc.)")
Lrow = InputBox("Enter row number of first data cell.")
Urow = InputBox("Enter row number of last data cell.")
Cnum = InputBox("Enter column NUMBER for output data (A=1, B=2 etc.)")
For i = (Lrow + 14) To (Urow - 14)
Cells(i, Cnum).Value = (-351 * Cells(i - 14, Ic).Value - 216 * Cells(i - 13, Ic).Value + -91 * Cells(i - 12, Ic).Value _
+ 24 * Cells(i - 11, Ic).Value + 129 * Cells(i - 10, Ic).Value + 224 * Cells(i - 9, Ic).Value _
+ 309 * Cells(i - 8, Ic).Value + 384 * Cells(i - 7, Ic).Value + 449 * Cells(i - 6, Ic).Value _
+ 504 * Cells(i - 5, Ic).Value + 549 * Cells(i - 4, Ic).Value + 584 * Cells(i - 3, Ic).Value _
+ 609 * Cells(i - 2, Ic).Value + 624 * Cells(i - 1, Ic).Value + 629 * Cells(i, Ic).Value _
+ 624 * Cells(i + 1, Ic).Value + 609 * Cells(i + 2, Ic).Value + 584 * Cells(i + 3, Ic).Value _
+ 549 * Cells(i + 4, Ic).Value + 504 * Cells(i + 5, Ic).Value + 449 * Cells(i + 6, Ic).Value _
+ 384 * Cells(i + 7, Ic).Value + 309 * Cells(i + 8, Ic).Value + 224 * Cells(i + 9, Ic).Value _
+ 129 * Cells(i + 10, Ic).Value + 24 * Cells(i + 11, Ic).Value - 91 * Cells(i + 12, Ic).Value _
- 216 * Cells(i + 13, Ic).Value - 351 * Cells(i + 14, Ic).Value) / 8091
Next i
Exit Sub
NotValidInput:
MsgBox ("Non valid entry- terminating.")
End Sub