I would advise uncommenting the application.volatile because otherwise the
function will not return the correct answer when the value in the second
cell (the one referenced by the offset) is changed.


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Tom Ogilvy" wrote in message
...
I would be interesting to know what makes you think it is slow. For me,
both mine and yours are pretty much instantaneouls and both update when I
change the per in a cell and pass it in as a cell reference (assume that
is
what you are talking about. The procedure is simple so I am not sure
what
optimzations you would do. Another approach:

Function ROC(val1 As Range, per As Variant)
' Application.Volatile
If val1.Row - per < 1 Then
ROC = ""
Else
ROC = val1 - val1.Offset(-1 * per + 1, 0).Value
End If
End Function

Yours causes an problem if the address resolves to less than row 1 because
you try to use it before you test for that condition.



--
Regards,
Tom Ogilvy


"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