If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Solver vs. Exponential Trendline
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Solver vs. Exponential Trendline

#1
December 21st 05, 07:08 PM posted to microsoft.public.excel.misc
 jcoleman52 external usenet poster Posts: n/a
Solver vs. Exponential Trendline

Can anyone (briefly) compare and contrast these two Excel features? We
have a set of data from a study for which we are trying to plot a decay
curve with an accompanying half-life calculation. One option is to
create a plot of the data with an exponential trendline (Y=b*exp^cx).
Another is to use the Solve add-in, utilizing the same equation, and
minimize the sum of the squared deviations by manipulating the
regression coefficients (b and c). Both methods seem to yield a curve
that gives a reasonable approximation of the observed data, but with
slightly different rate coefficients, which will of course yield
slightly different half-lives. Any thoughts on which approach is more
appropriate? Thanks.

--
jcoleman52
------------------------------------------------------------------------
jcoleman52's Profile: http://www.excelforum.com/member.php...o&userid=29498

#2
December 21st 05, 07:36 PM posted to microsoft.public.excel.misc
 FinRazel external usenet poster Posts: n/a
Solver vs. Exponential Trendline

In this case, you should probably choose your curve based on which one most
closely calculates the half-life of a substance having a known (published)
half-life similar to the half-lives that you found experimentally.
--
Anne Murray

"jcoleman52" wrote:

>
> Can anyone (briefly) compare and contrast these two Excel features? We
> have a set of data from a study for which we are trying to plot a decay
> curve with an accompanying half-life calculation. One option is to
> create a plot of the data with an exponential trendline (Y=b*exp^cx).
> Another is to use the Solve add-in, utilizing the same equation, and
> minimize the sum of the squared deviations by manipulating the
> regression coefficients (b and c). Both methods seem to yield a curve
> that gives a reasonable approximation of the observed data, but with
> slightly different rate coefficients, which will of course yield
> slightly different half-lives. Any thoughts on which approach is more
> appropriate? Thanks.
>
>
> --
> jcoleman52
> ------------------------------------------------------------------------
> jcoleman52's Profile: http://www.excelforum.com/member.php...o&userid=29498
>
>

#3
December 21st 05, 08:39 PM posted to microsoft.public.excel.misc
 B. R.Ramachandran external usenet poster Posts: n/a
Solver vs. Exponential Trendline

Hi,

The Solver result is technically 'more' correct. Because, I guess, you
would have used the exponential equation [y = b*exp(c*x)] with some guess
values of b and c (c should have been negative) to calculate y values,
calculated the SSR [sum of y(experimental) - y (calculated)], and minimized
the SSR by optimizing b and c. This approach uses the raw data 'as is' and,
therefore, the result is more trustworthy.

The exponential trendline, on the other hand, first linearizes the data, ln
y = ln b + c*x, does a LINEAR regression, calculates the slope (c) and
y-intercept (ln b) for the best linear fit, and SHOWS the trendline equation
in the exponential form (which is y = exp(y-intercept) exp(c*x). It does
this by minimizing the SSR of (not the original y data) for the
'transformed', (i.e., ln y) data.

If the experimental data are absolutely free of errors (uncertainties)
[which is never the case], the two results WILL be identical (the minimized
SSR will be zero in both cases). Real-life data, however, contain
uncertainties, and the linear transformation of the data DOES NOT transform
the errors appropriately [UNLESS YOU DO A WEIGHTED REGRESSION].

To verify this, calculate the natural logarithm of y, and fit the ln(y),x
data to the linear equation, ln y = ln b +c*x, using Solver. You would
notice that the b and c values you obtain would pretty much correspond to the
results from the exponential trendline fit.

Regards,
B. R. Ramachandran

"jcoleman52" wrote:

>
> Can anyone (briefly) compare and contrast these two Excel features? We
> have a set of data from a study for which we are trying to plot a decay
> curve with an accompanying half-life calculation. One option is to
> create a plot of the data with an exponential trendline (Y=b*exp^cx).
> Another is to use the Solve add-in, utilizing the same equation, and
> minimize the sum of the squared deviations by manipulating the
> regression coefficients (b and c). Both methods seem to yield a curve
> that gives a reasonable approximation of the observed data, but with
> slightly different rate coefficients, which will of course yield
> slightly different half-lives. Any thoughts on which approach is more
> appropriate? Thanks.
>
>
> --
> jcoleman52
> ------------------------------------------------------------------------
> jcoleman52's Profile: http://www.excelforum.com/member.php...o&userid=29498
>
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Trouble with Solver Wanderingspirit99 Excel Discussion (Misc queries) 0 September 12th 05 03:50 PM Using Solver with VBA EggShell Excel Discussion (Misc queries) 2 August 22nd 05 07:06 AM Trendline Extract Phil Hageman Charts and Charting in Excel 5 July 6th 05 02:27 AM Excel option to store trendline's coefficients in cells for use Miguel Saldana Charts and Charting in Excel 9 June 20th 05 08:45 PM How do I get the trendline equation from Excel to script? Mattias Charts and Charting in Excel 1 December 7th 04 12:21 PM

All times are GMT +1. The time now is 02:57 PM.