Fit non-linear function

**JonnyB** · 02-12-2010, 01:47 AM

Hi-

I have a table of data that was generated by doing monte carlo simulations which were driven by two variables (the attached shows variable values as the row and column labels with results in the body of the table). I'm wondering if anyone can help me figure out a way to determine a non-linear formula that i can use to best represent the data? Unfortunately, interpolating isn't an option because I need to be able to use the closed form function in other applications. Very much appreciate any help.

Thanks,
JonnyB

**teylyn** · 02-12-2010, 02:08 AM

Hi JohnnyB,

welcome to the forum.

If you chart each row of data in an XY chart, using row 2 as X values and one of the data rows as Y values, you can then add a polynomial trendline with an order of 6, display the equation on the chart and there's your non-linear formula.

**JonnyB** · 02-12-2010, 02:58 AM

Thank you.

And thanks for such a prompt response. I hate to expose myself as dense in just my second post

, but I've tried fooling around with this a bit and its not clicking. As an end result, I'm looking for an equation where I can input a value from row 2 and a value from column B and have returned a value that is very close to where they intersect in the original table of data - I'm just not seeing how the above takes into account all the values in column B or more than one row of data? I feel like I'm missing obvious...again, I really appreciate the help.

**teylyn** · 02-12-2010, 03:56 AM

Please do not quote whole posts. Only quote when you're not responding to the immediate last post, and then only quote the pertinent bits.

If you input a value from row 2 and a value from column B, then you can use a simple index/match combo to return exactly that intersect from the table. Depending on how you set up the index/match combo, it will return the closest match if you enter numbers that are in between the values of row 2 and/or column B.

Such a formula looks like

=INDEX(C3:Z43,MATCH(Row2_Value,B3:B43,1),MATCH(ColumnB_Value,C2:Z2,1))

**JonnyB** · 02-12-2010, 04:03 AM

Apologies.

Right, that is something I actually do know how to do, but unfortunately as I mentioned, I need a closed form equation for use outside of excel. Was hoping that I could use excel to determine what that might be.

**pike** · 02-12-2010, 06:11 AM

The equation for a line is y = mx + b

where m=INDEX(LINEST(known_y's,known_x's),1)

and b=INDEX(LINEST(known_y's,known_x's),2)

you can now find x or y

hope it helps

**shg** · 02-12-2010, 11:35 AM

See http://www.excelforum.com/excel-gene...egression.html

**JonnyB** · 02-12-2010, 03:16 PM

Thanks shg - that looks like it does exactly what i'm looking for. Any pointers on how to get from the coefficients table to an equation of the form z = ...y + ...x + ....? (having trouble following the matrix mult functions)