+ Reply to Thread
Results 1 to 5 of 5

Use cell value in a cell reference

  1. #1
    Registered User
    Join Date
    02-21-2011
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    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. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    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. #3
    Registered User
    Join Date
    02-21-2011
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    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. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    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. #5
    Registered User
    Join Date
    02-21-2011
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2007
    Posts
    3

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1