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

  • 2
    $\begingroup$ Take a look at NonlinearModelFit in the documentation. $\endgroup$ – Szabolcs Feb 15 '14 at 0:13
  • $\begingroup$ 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). $\endgroup$ – BlBl Feb 16 '14 at 18:40
  • $\begingroup$ 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. $\endgroup$ – BlBl Feb 16 '14 at 18:41
  • $\begingroup$ 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. $\endgroup$ – 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])
4.84687 x^1.97633

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.