MATLAB vs. Excel: Matlab seems to give better answer to exponential decay, anyone know why?

20 次查看（过去 30 天）
Marc
Marc 2011-3-16
A co-worker of mine plotted the data (pHi1,alf) below in Excel and used the "add trendline" to fit the data. Excel returns "y = 2814.2e-3.5613x" and the fit is not very good.
I used the following Matlab code to fit the data and of course, the fit is much better.
As I am trying to explain to my co-workers the advantages of Matlab, does anyone know why Excel does such a poor job with this?
Although better can be subjective, is there something obvious that I am doing "wrong" in excel or is the answer simply "You get what you pay for"?
pHi1 = [0.063 0.113 0.22 0.286 0.373 0.437 0.53 0.547 0.709 0.938 1.72];
alf = [4620 3390 2440 1460 840 410 220 190 90 40 20];
%set up dissolution model
%exponential decay
aldis = @(dis,xx)(dis(1)*exp(-dis(2)*xx));
%initial guess
dis = [alf(1) 2];
%use nlinfit for least squares to determine dis(1) and dis(2) in disfit
disfit = nlinfit(pHi1,alf,aldis,dis)
pHfit = min(pHi1):0.1:max(pHi1);
alfit = aldis(disfit,pHfit);
figure(1)
plot(pHi1,alf,'ok','markerfacecolor','r','markersize',4)
hold on
plot(pHfit,alfit,':b')
hold off
xlabel('Initial pH')
ylabel('Dissolved Alumina by ICP (ppm)')
  1 个评论
Lynn Knoblauch
Lynn Knoblauch 2012-6-30
Your script and comments is something that I have been wondering a lot about too and I want to thank you in that it has answered a question I had. The other question I have that you may know.....do you know how to get the horizontal asymptote value? Thanks

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Matt Tearle
Matt Tearle 2011-3-16
Excel is apparently doing a log of the y data then fitting a line. This approach is very sensitive to outliers. Change your plot commands to semilogy and you'll see why with your data. That last point is an outlier in semilog space, even though it isn't so much in the original variables.
To see what Excel did (in MATLAB, if that makes sense):
c=polyfit(pHi1,log(alf),1);
c(1)
exp(c(2))
Look familiar? :)
hold on
semilogy(pHfit,exp(polyval(c,pHfit)),'--')

更多回答（2 个）

the cyclist
the cyclist 2011-3-16
The trend command in Excel just does a linear fit, right? Did you try to fit a linear function to exponential data? (If so, that's why the Excel fit was poor.) Or did you take the log of the exponential data before fitting in Excel?
  1 个评论
Marc
Marc 2011-3-16
The fit from excel was using the "Add Trendline" by right clicking on the data series in the plot and then clicking on "exponential fit".
Not sure what Excel does behind the scene??

请先登录，再进行评论。

Richard Crozier
Richard Crozier 2011-3-17
You should never use Excel for anything more complicated than accounting, as this only requires a precision of 2 decimal places, and can be checked on a calculator. Anything else, and you basically can't trust the result.
Anyone attempting to present statistics done in Excel should be particularly embarrassed.

类别

在 Help Center 和 File Exchange 中查找有关 Data Preprocessing 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by