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_(i1)  a_(ik) 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_(i1)  a_(ik) 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(ik) 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


21 = 1
31 = 2
52 = 3
83 = 5
For the same values in column A, but k=3, I want B to be:



31 = 2
51 = 4
82 = 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 hardcoded the value from C1? You're trying to explain it in terms of math, not Excel. "Ai  A(ik)" is not a valid formula in Excel.
=(A1*5)(A1*(5C1))
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 2 users browsing this thread. (0 members and 2 guests)
Bookmarks