+ Reply to Thread
Results 1 to 6 of 6

Formula copied to cell based on another cell value

  1. #1
    Registered User
    Join Date
    06-05-2013
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula copied to cell based on another cell value

    This is a modification of an old worksheet where I would use HLOOKUP to look in the table (shown as old table) and based on the value in column B of the worksheet, I would find the data below and above it and then use interpolation to fill in the value in column C. The column of the old table that was selected was determined by the value in D5. For example, if D5 is 1.8, HLOOKUP function was used along with the value 25 in B8 to select data1 and data2 under the "1.8" column in the old table. Then I would interpolate between data1 and data2 and this result was placed in C8.

    However now I have gotten equations that have replaced the data in the table. I was still going to use the HLOOKUP function to select the equations from "new table" based on the value in D5, but I can't figure out how to do this since you can't enter a function as f(x) = 3125 * power (x,-1.45) and then have C8 contain this function evaluated using B8 for x. I am have searched on ways to do this but I can't even find a good way to describe what I want to do so I get lots of irrelevant responses.

    I am open to ditching the table and having a drop down menu for D5 and depending on what I select having the correct formula filled in for column C, but I am running in to issues with that method as well. Thanks for any help...BTW, I am using Excel 2007.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formula copied to cell based on another cell value

    Quote Originally Posted by lemmy999 View Post
    ... but I can't figure out how to do this since you can't enter a function as f(x) = 3125 * power (x,-1.45) and then have C8 contain this function evaluated using B8 for x. I am have searched on ways to do this but I can't even find a good way to describe what I want to do so I get lots of irrelevant responses.

    ...
    While you are correct that you can't "drop" a variable into the middle of an HLOOKUP "formula, what you can do is add another row to the lookup table, and separate the formula into before and after the variable and operators.

    See attached for example...

    The question then progresses into whether each formula uses the same operators to each side of the variable???
    Attached Files Attached Files
    Last edited by jhren; 06-05-2013 at 01:22 AM.

  3. #3
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formula copied to cell based on another cell value

    If you want to do what I suggested previously and put the adjacent operators in the table, you can use the built-in Excel 4.0 macro function EVALUATE()...and the file will have to be saved as macro-enabled.

    See B8's comment in attached file.

    PS: your profile says you are using 2007. I think this works in 2007, don't know for certain.
    Attached Files Attached Files
    Last edited by jhren; 06-05-2013 at 05:21 AM.

  4. #4
    Registered User
    Join Date
    06-05-2013
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula copied to cell based on another cell value

    Quote Originally Posted by jhren View Post
    While you are correct that you can't "drop" a variable into the middle of an HLOOKUP "formula, what you can do is add another row to the lookup table, and separate the formula into before and after the variable and operators.

    See attached for example...

    The question then progresses into whether each formula uses the same operators to each side of the variable???
    Ok, I see what you have done. In the table you just put the coefficients, but then actually build the equation in the C column. That should work very nicely if the form of the equation for all columns in the table is always the same...for example f(x) = A * x ^ b. I think that will be the case in my application, so this should work. Thanks!

  5. #5
    Registered User
    Join Date
    06-05-2013
    Location
    TN
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula copied to cell based on another cell value

    I am still looking at your second example. But what if the form of the equation was completely different for each column in the look up table? For example what if D5 was 1.8 and the equation was f(x) = A * x ^b, but if D5 was 1.6 the equation was f(x) = A * e ^ (b/x) + B * x ^ c? Is there any way to have the generic form of the equation also stored in the look up table and then transferred to the C column based on D5?

  6. #6
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formula copied to cell based on another cell value

    Perhaps... getting too complex for my brain at this instance (up too long).

    See if the tutorial at the following link helps...

    http://www.vertex42.com/ExcelArticle...-function.html

+ 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