# Formula copied to cell based on another cell value

1. ## 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.

2. ## Re: Formula copied to cell based on another cell value

Originally Posted by lemmy999
... 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???

3. ## 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.

4. ## Re: Formula copied to cell based on another cell value

Originally Posted by jhren
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. ## 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. ## 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

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

#### 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