I need to use the value of a cell in a cell reference. For example, suppose I want to use a formula like
a_i = a_(i-1) - a_(i-k) where k is a value stored in a cell somewhere so that I can vary it and see how things change.
How can I do this?
I need to use the value of a cell in a cell reference. For example, suppose I want to use a formula like
a_i = a_(i-1) - a_(i-k) where k is a value stored in a cell somewhere so that I can vary it and see how things change.
How can I do this?
Welcome to the forum.
If you need to reference another cell in a formula, just type the cell address in the formula. For example, in cell A1 you want to reference a value in cell C5. In A1 type:
=18+(20/C5)
This says: Divide 20 by the value in cell C5, then add 18.
Thanks...I understand how to reference a cell in a formula. My issue is that I want to use the value in a cell to determine the row number in the formula.
Suppose I have a list of values in column A. I'd like to compute column B based on column A. However, the values in column B are based on another parameter that is stored in say, cell C1 (so that I can change it and see different results).
I'd like to compute the cell in row i of column B as follows:
Ai - A(i-k) where k is the value in cell C1.
For example, if A is:
1
1
2
3
5
8
and k is 2, then B would be computed as
--
--
2-1 = 1
3-1 = 2
5-2 = 3
8-3 = 5
For the same values in column A, but k=3, I want B to be:
-
-
-
3-1 = 2
5-1 = 4
8-2 = 6
Hope that helps to clear up my question. Thanks much for your help!
What FORMULA would you use to calculate the value in B if you hard-coded the value from C1? You're trying to explain it in terms of math, not Excel. "Ai - A(i-k)" is not a valid formula in Excel.
=(A1*5)-(A1*(5-C1))
That is a valid formula where i = 5, k = the value in C1, and A1 contains some other value.
I'm not sure if this answers your question, but the INDIRECT function will allow you to create a cell reference based on another cell's value, for example:
If cell C1 has the value 5. Then the following formula will return the result from cell A5:
=INDIRECT("A"&C1)
This becomes
=INDIRECT("A5")
which then gets the value from A5.
Thanks for the info on the INDIRECT function. That is VERY helpful. So here's where I am now (I've converted to RC notation...seems to be easier for what I'm doing).
INDIRECT("R24C",0) gives me the value I want to use...so far, so good...but the thing I want to do is this:
R [ -INDIRECT("R24C",0) ] C[-1]
and Excel doesn't like me using the INDIRECT("R24C",0) as the row offset (much less *negative* INDIRECT("R24C",0) which is what I really want).
Any ideas on this?
Thanks again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks