It does not recalc for me when i change the cell referred to by the offset,
unless i have application.volatile:

For example using =roc(A4,A1) it does recalc if either cell A4 or A1 are
changed, but if A1 contains 3 it does not recalc when A2 is changed.

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

"Tom Ogilvy" wrote in message
...
It updated for me with both versions of the function (without
application.Volatile)

=roc(A4,A1)


I thought the same, but I can't argue with results. Perhaps the function
didn't be recomiled after I commented out hte application.volatile, but I
pasted in the OP's version (with a different function name) and it updated
as well

--
Regards,
Tom Ogilvy


"Charles Williams" wrote in message
...
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