| |
 |
|
|
Science Forum Index » Statistics - Education Forum » can excel solver do this
Page 1 of 1
|
| Author |
Message |
| Amit |
 Posted: Fri Dec 08, 2006 6:08 pm |
|
|
|
Guest
|
Hello everyone,
Could somebody please let me know if this is possible to calculate in
excel solver
If I have an equation in the form
D = h^a * k^b* L^c
h, k and L are know variables and a, b c are unknown variables within
limits
a <=2, b<=3 and c<=5,
Can I find a, b, c by using excel solver from some data D' Vs S where S
is also known variable and D is to be optimised for every S against D'.
Basicallt ecel solver gives me a, b and c for D to be closest to D'.
thanks
Amit |
|
|
| Back to top |
|
| Scott Seidman |
| Posted: Fri Dec 08, 2006 6:32 pm |
|
|
|
Guest
|
"Amit" <amitroorkee@gmail.com> wrote in news:1165615710.978513.38200@
79g2000cws.googlegroups.com:
Quote: Hello everyone,
Could somebody please let me know if this is possible to calculate in
excel solver
If I have an equation in the form
D = h^a * k^b* L^c
h, k and L are know variables and a, b c are unknown variables within
limits
a <=2, b<=3 and c<=5,
Can I find a, b, c by using excel solver from some data D' Vs S where S
is also known variable and D is to be optimised for every S against D'.
Basicallt ecel solver gives me a, b and c for D to be closest to D'.
thanks
Amit
I don't know if excel can do this directly, but it shouldn't have a
problem if your data is amenable to doing a simple log transformation.
Look how nice things get if you take the log of each side of your
equation!
--
Scott
Reverse name to reply |
|
|
| Back to top |
|
| David A. Heiser |
| Posted: Sat Dec 09, 2006 4:18 pm |
|
|
|
Guest
|
"Scott Seidman" <namdiesttocs@mindspring.com> wrote in message
news:Xns9893B25EA52C0scottseidmanmindspri@130.133.1.4...
Quote: "Amit" <amitroorkee@gmail.com> wrote in news:1165615710.978513.38200@
79g2000cws.googlegroups.com:
Hello everyone,
Could somebody please let me know if this is possible to calculate in
excel solver
If I have an equation in the form
D = h^a * k^b* L^c
h, k and L are know variables and a, b c are unknown variables within
limits
a <=2, b<=3 and c<=5,
Can I find a, b, c by using excel solver from some data D' Vs S where S
is also known variable and D is to be optimised for every S against D'.
Basicallt ecel solver gives me a, b and c for D to be closest to D'.
thanks
Amit
++++++++++++++++++++++++++++++++++++++++
NO NO NO
I am somewhat an expert on Excel in this area. See my URL here and the
section on non-linear regression.
www.daheiser.info/excel/frontpage.html
SOLVER FAILS almost everyone of the NIST data sets on non-linear regression.
There are several literature sources on this. I discuss the failure of
SOLVER.
Two, theire is a real problem trying to properly fit a power-product model
to linear regression by taking the logs. You are fitting the logs, not the
original data, and the resulting log model (in most cases) poorly fits the
original, untransformed data. Hesse has been reporting on this problem for
some time.
You should go directly into a non-linear fitting package such as the free
addin from Volpi (described in note AA). The Levenber-Marquardt algorithm
really works well on these types of models. The problem is you have to
program the function and all the first derivatives (with respect to
parameter values). This puts a real strain on typical investigators because
they have to know calculus and Excel's Visual Basic package.If you don't
want to do this, you can stick with the data and the equation as related
cells, and use Volpi's Downhill-Simplex to find optimum values for the
coefficients. However this does get trapped in local minimums.
I have used these techniques to fit Vacuum Tube and FET characteristic curve
data to complex equations with really good results. I have found some very
interesting ultra-linear circuit structures using these techniques in Excel.
David Heiser
All I can say is that using Volpi's routines I can beat JMP 4.0 on the NIST
non-linear sets. |
|
|
| Back to top |
|
| Scott Seidman |
| Posted: Mon Dec 11, 2006 10:56 am |
|
|
|
Guest
|
"David A. Heiser" <dah_box1@innercite.com> wrote in
news:eSEeh.52$to1.126076@news.sisna.com:
Quote: Two, theire is a real problem trying to properly fit a power-product
model to linear regression by taking the logs. You are fitting the
logs, not the original data, and the resulting log model (in most
cases) poorly fits the original, untransformed data. Hesse has been
reporting on this problem for some time.
That's why I said "If your data is amenable"
The error needs to be multiplicative, and not additive.
D=h^a*k^b*L^c*error
logD= alogh+blogk+clogL+log(error).
In general, though, my experience is that the log transformation shown as
the typical example in the transformation chapters of OLS textbooks is only
useful until you actually try to use it, and you get all the log of
negative numbers warnings.
--
Scott
Reverse name to reply |
|
|
| Back to top |
|
| |
|
Page 1 of 1
All times are GMT - 5 Hours
The time now is Wed Dec 03, 2008 10:21 pm
|
|