# Use cell value in a cell reference

1. ## Use cell value in a cell reference

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?

2. ## Re: Use cell value in a cell reference

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.

3. ## Re: Use cell value in a cell reference

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!

4. ## Re: Use cell value in a cell reference

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.

5. ## Re: Use cell value in a cell reference

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.