Function ROC(val1 As Range, per As Integer) as Double
dim thisrow as long
on error goto trap
thisrow = val1.row - per




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
exit function
trap:msgboxerror

End Function

-----Original 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


.