Hi Max
For the recalculation, Tom added "application.volatile"
Look in help
Regards,

JY

"Max" wrote in message
...
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 Then
R0C = ""
Else

ROC = (val1 - val2)
End If

End Function


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 appreciated

Max