Function to return Formula from Cell Value

**1michael** · 07-14-2014, 12:12 PM

I have inserted some code to extract the trend line formula (J40) from a graph and then have it separated out in the adjacent columns with a series of formulas. In cell N40 I have my formula to calculate the forecast for the 6th period for a logarithmic trend line.

I want to avoid anymore VBA and do not want to have a manual find and replace to the first values in column M to insert an equals sign to calculate the forecast as I know this would work.
I am looking for a function or a formula so that this formula will automatically re-calculate if the numbers in the graph change.

Below is formulas I have and one I am needing. Attached is an image of output so far.

K40 = LEFT(J40,LEN(J40)-10
L40 = REPLACE(K40,FIND("ln(x) + ",K40,1),8,"*ln(L39)+")
M40 = SUBSTITUTE(L40,",","")
N40 = REPLACE(M40,1,4,"")
O40 = ???

Capture.JPG

**hoyasaxa215** · 07-14-2014, 12:30 PM

Seems like there should be a much easier way to go about this. Can you post a cleaned version of the excel file so we can see what you're trying to accomplish within the context of the data you have available?

**MrShorty** · 07-14-2014, 12:32 PM

I would use the LINEST() function to perform the regression directly in the spreadsheet, then the cell that calculates the forecast values can refer to the LINEST() output.

http://office.microsoft.com/en-us/ex...in=HA010277524

You will want to make the following "linear" connection:
your equation : y=A*ln(x)+B
let u=ln(x) and substitute: y=A*u+B <--- you should be able to recognize that this is a linear function in u. Your known_x argument in the LINEST() function will need to perform this substitution in one way or another.

**1michael** · 07-14-2014, 12:47 PM

See attached for a cleaned up version.

You may be right, but I am going to have to have a work tab for this project anyways so doesn't matter if its too dirty but open to suggestions. Just has to update automatically after running ctrl + l macro which updates the logarithmic trendline formula based on the data from above.

In this case I am looking for more than a linear forecast. Will be needing to use polynomial, logartihmic, etc and this is only way I know to extract necessary formula. Linest restricts me to just linear.

**MrShorty** · 07-14-2014, 12:58 PM

In this case I am looking for more than a linear forecast. Will be needing to use polynomial, logartihmic, etc and this is only way I know to extract necessary formula. Linest restricts me to just linear.

It takes some "upper level" math to get the formal definitions and proof of exactly what consititutes a "linear" function. Suffice it to say that every one of the the available chart trendlines is or can be transformed into a "linear" equation -- and can therefore be regressed using LINEST()

examples:
polynomial: y=a+bx+cx^2+dx^3...
let u=x^2, v=x^3,... and substitute: y=a+bx+cu+dv... -- a multivariable linear regression (LINEST() help file has an example of a multivariable regression).

exponential: y=A*exp(Bx)
take log of both sides: ln(y)=ln(A) + x*B
make substitutions, if needed to see: u=ln(y), k=ln(A) -> u=k+Bx -- another linear function.

I still stand by my advice: linearize your desired regression equations, then perform the regressions directly in the spreadsheet using LINEST().

**1michael** · 07-14-2014, 02:43 PM

Thanks but this still does not answer my initial question.

I am not a pro in statistics hence why I developed this easy workaround that lets me plug no forecast period into the exact formula excel has came up with.

Just needing to figure out if there is a function or formula I can use in cell F21 to actually take the formula from E21 and solve it without any extra vba and/or manual manipulation.

**hoyasaxa215** · 07-14-2014, 02:54 PM

I broke out the components of your equations (manually typed the numbers into the cells around the equation cell) and included an "=indirect" reference to L39. However, L39 was blank, so I just typed in a random number. If there's a number in L39, the calculation will complete. I didn't spend enough time to figure out where the different numbers were coming from, but can definitely create a few other formulas to extract the components of the equation i manually entered. If that's something you can't do.

**hoyasaxa215** · 07-14-2014, 03:11 PM

If the last post solves your question, please mark the thread as "Solved".

**1michael** · 07-14-2014, 05:50 PM

I think that is what I need to do, break it down a little more. Thanks hoya.

Function to return Formula from Cell Value

LinkBack

Thread Tools

Rate This Thread

Display

Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Re: Function to return Formula from Cell Value

Thread Information

Users Browsing this Thread

Similar Threads

formula to return blank cell - IF function

function CELL() to return the formula in the referenced cell

[SOLVED] function CELL() to return the formula in the referenced cell

function CELL() to return the formula in the referenced cell

[SOLVED] function CELL() to return the formula in the referenced cell

Bookmarks

Bookmarks

Posting Permissions