Excel Help

Jokester · 7 Aug 2013 at 21:07

Bit stuck on how to do this in excel, what I want to do is create a rolling average type calculation.

Cell A = My new value
Cell B = My old value
Cell C = 0.2x Cell A + 0.8x Cell B

The trick that I want is that when I enter a new value in Cell A I want it to calculate the new value in Cell C, copy it to Cell B and then clear Cell A ready for entering a new value.

Does this make sense?

dazzerd · 7 Aug 2013 at 21:34

You need to enter this code into the worksheet module (object) of your workbook, which I am assuming is called sheet1. The code also assumes your values are in range A1, B1 and C1.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rangeA As Range
Dim rangeB As Range
Dim rangeC As Range
Dim rangeAOldvalue As Double
Dim rangeBOldvalue As Double

Set rangeA = ThisWorkbook.Sheets("Sheet1").Range("A1")
Set rangeB = ThisWorkbook.Sheets("Sheet1").Range("B1")
Set rangeC = ThisWorkbook.Sheets("Sheet1").Range("C1")

If Target.Address = rangeA.Address Then
Application.EnableEvents = False
rangeAOldvalue = rangeA.Value
rangeBOldvalue = rangeB.Value
rangeB.Value = rangeAOldvalue
rangeA.Value = vbNullString
rangeC.Value = (0.2 * rangeAOldvalue) + (0.8 * rangeBOldvalue)
Application.EnableEvents = True
End If

Set rangeA = Nothing
Set rangeB = Nothing
Set rangeC = Nothing

End Sub

Jokester · 7 Aug 2013 at 21:53

Cheers, doesn't quite do what I'm looking for, but I can see how it works so I should be able to tweak it.

dazzerd · 7 Aug 2013 at 22:03

Yeh I think you may need to be moving the result returned into cell C into cell B rather than moving the value in cell A to cell B.