Below I have a function calculates the difference of 2 values based on a cell reference and an interval
For example: =ROC(A10,3) calculates the difference between A10 - A8 (which is what I'm after).....

Function ROC(val1 As Range, per As Integer

Set val2 = Cells(Range(val1.Address).Row - per + 1, Range(val1.Address).Column
If Range(val1.Address).Row - per < 1 The
R0C = "
Els

ROC = (val1 - val2
End I

End Functio

There are 2 problems with the above code

1) When the formula is first entered it calculates correctly, however when the 2nd parameter is referred to by reference the formula does not recalculate correctly when the value in this cell is changed. It infact shows the value of val1....I have checked the options and calculation is set to automatic and iterations are on and to 100..

2) It seems quite slow to run. Can it be optimised in anyway

Any help would be appreciate

Ma