# Replicating Excel output using Mathematica

I have 2 columns of data in Excel. One is inputs the other is outputs. I ask Excel to make a graph of that data and to fit the data to a power trendline. Excel does this and it's fine as far as it goes but I'd like to get Mathematica to give me a similar equation for a line.

How do I do that?

If it helps the ordered pairs are:

{0.473, 1.1}, {0.4825, 1.15}, {0.492833333333333, 1.2}, {0.503666666666667, 1.25},
{0.513666666666667, 1.3}, {0.5245, 1.35}, {0.533, 1.4}, {0.543166666666667, 1.45}


Excel gives it as y=4.8549x^1.9788

• Take a look at NonlinearModelFit in the documentation. – Szabolcs Feb 15 '14 at 0:13
• Thanks for the reply. While this doesn't get the exact equation that Excel does it does get me something. Originally I thought of just saving the ordered pairs of inputs and outputs as data and then fitting data to a parabola. That didn't give me what I wanted (it gave me 4.80984 t^2 + 0.0585315 t). I then tried to fit the data logarithmically by doing logdata=Log[data], then doing Fit[logdata, {1, t}, t]. Doing it this gave me something interesting: (1.57999 + 1.9788 t). – BlBl Feb 16 '14 at 18:40
• I could see that the "1.9788" part was matching Excel's exponent so I focused on the ≈1.58 part and did: Solve[Log[A] == 1.5799945602822283 , A] which gave me exactly the base that Excel did. So right now I think I'm just looking for a shorter way to put all of that together. The NonlinearModelFit does look like it'll give me something short though. – BlBl Feb 16 '14 at 18:41
• Excel probably uses a different method: it takes the logarithm of the data and fits a linear model to it: LinearModelFit[Log[data], {1, x}, x]. This method gives the exact same result as Excel. (Note that this will minimize a different quantity as it's fitting transformed data!) Typically when people fit power laws, they have data over several orders of magnitude. Here this is not the case, so I suggested NonlinearModelFit. In fact it seems clear that your data conforms to x^2, so you can consider fitting only a x^2 instead. – Szabolcs Feb 16 '14 at 18:43

As Szabolcs writes in his comment: use NonlinearFit.

data =
{
{0.473, 1.1}, {0.4825, 1.15}, {0.492833333333333, 1.2}, {0.503666666666667, 1.25},
{0.513666666666667, 1.3}, {0.5245, 1.35}, {0.533, 1.4}, {0.543166666666667, 1.45}
};
model = NonlinearModelFit[data, a x^b, {a, b}, x]

FittedModel[4.84687 x^1.97633]


NonlinearModelFit returns a fitted model object which contains much more information than just the fitted function. To extract the function, evaluate

y[x_] = (model["Function"][x])
y[x]

4.84687 x^1.97633
`