Referring to "previous cell" in a formula in a "varying current ce

[arasan77]

I need to calculate a value using a formula that uses the reference to a
previous cell (e-g. "A10", when the formula used is for computing "A11"; in
the next row "A11" needs to be used for calculating "A12" and so on...), when
the cell in which the formula to be used keeps varying. For example, I need
to compute today's gain or loss compared to the previous day's, on a daily
basis in real time. Is there any way to do this in Excel 2003? In other
words, can I use the equivalent of "contents of cell A(i)" in the formula
contained in cell "A(i+1)", as could be used in any programming language?
Thanks for all, who could provide me an answer.

 

[Chip Pearson]

Using simply a formula, you could use

=IF(COLUMN()=1,NA(),INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

This returns the value in the column to left of the cell in which the
formula appears. If the formula is in column A, there is nothing to
the left, so the result is #N/A.

In code, you could do something like the followng.

Function Test(D As Double) As Variant
On Error Resume Next
Test = D * Application.Caller.Offset(0, -1).Value
If Err.Number <> 0 Then
Test = CVErr(xlErrNull)
End If
End Function

This uses Application.Caller to get the reference to the cell that
called Test, then uses Offset to get the value that is one column to
the left to the cell that called it.

Note, though, that since the cell to the left of Application.Caller
(which is the cell from which this function was called) is not a
direct parameter to Test, Excel will not automatically recalculate the
function if the value to the left is changed. E.g, if the function
Test appears in D1 as =TEST(A1), the result is A1*C1. However, if you
change C1, Excel does not recognize this as a change that affects the
TEST function, so it will not recalculate the result.

As a general rule, all inputs to a function written in VBA that will
be called from a worksheet cell should be passed in as a parameter.
Otherwise, you may get inconsistent results. E.g, a function like

Function ABC() As Double
ABC = Range("A1").Value * 10
End Function

is bady written because Excel won't recalc this function when A1 is
changed because Excel has no knowledge of what ABC does. Instead, you
would want to write

Function ABC(X As Double) As Double
ABC = X * 10
End Function

This will ensure that when X is changed, ABC will be recalculated.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Barb Reinhardt]

Try recording a macro when you've entered the correct formula in a cell and
see what you get.