Solved

Fitting a Model to a set of data

Posted on 2000-04-25
14
281 Views
Last Modified: 2008-02-26
Hi.
I have a set of data that needs to fit this regression model below:
y(x)=a*[cosh(b*(c-x))/cosh(d)]
in order to find a, b, c, d and correlation coefficient for the regression
Can I do this using excel? How?

ps.
for those who don't know:
cosh = Hyperbolic cosine = (e^x+e^-x)/2
0
Comment
Question by:calab
  • 7
  • 6
14 Comments
 
LVL 13

Expert Comment

by:cri
Comment Utility
Does it have to be Excel ? MathCAD or similar programs are better suited for this. If yes: How many points do you have ?
0
 

Author Comment

by:calab
Comment Utility
I doesn't have to be Excel. I'm not familiar with MathCad, though. What about Matlab?
About my points:
I've just signed in. So, I still got 25 points left.
 
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
calab, I am not _that_ point horny... I was refering to the number of data points x,y pairs. Can you paste them here ?

I never worked with Matlab.
0
 

Author Comment

by:calab
Comment Utility
Sorry if I misunderstood you.
The problem is that I have several set of data with different size to be collected still.
What about "Maple"? Do you work with it?

0
 
LVL 13

Expert Comment

by:cri
Comment Utility
calab: First time I hear about http://www.maplesoft.com/

As for the datapoints: A sample is good enough. Want to see whether I can put something together in Excel, eventually MathCAD. Does it have to be the a.m. model ?
0
 

Author Comment

by:calab
Comment Utility
Here goes the data:
x =[ 2 4 5.5 7.5 9.5 11.5 14.5 17.5 21.5 25.5 30.5 36 46 56 71 86 94 ]
y(x) =[3.8 3.5 3.2 2.8 2.6 2.4 2.2 1.9 1.6 1.3 1.1 .9 .6 .4 .2 .1 .1 ]

a.m. model ????
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
I've tried to find a good solution with the Excel solver.

After many fails, found one, but the solver works very erratic, that means will not come up with a good solution every time. I doubt if it would fit your needs as too difficult to set. In fact, I'm not able at the moment to reproduce the best solution I had up to now. The initial values of the four variables a, b, c and d are very important for the solver.

Just one tip, simplify the equation by only using three variables:

y(x)=e*(cosh(b*(c-x)))

with e = a * cosh(d)

a and cosh(d) are both constants, and will not change with x changing, so they should be excluded from your approach. You can always fill them in afterwards, in fact the couple will offer a infinite number of possibiliities.

I'll report back if I find any better.

Calacuccia
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 13

Expert Comment

by:cri
Comment Utility
Did not forget your question.

a) I can approximate _this_ data "fairly" well using a power function trendline (y=a*x^b) in Excel or a polynom of the 3th order. Could you paste the values again, this time with 2-3 decimal digits ? I want to see what is rounding and what is approximation error.

Did not see your question regarding the model. I mean does it have to be this correlation form ? If yes: Can you explain why ? Perhaps a better work-around can be found.  
0
 

Author Comment

by:calab
Comment Utility
A lot of things have happened since the last time I posted a note here.
First of all: I found out the that Maple is capable of fitting a data to a hyperbolic function as simple as y(x)=a*cosh(x)+b.
Also, I found out that the sample I posted is completely wrong.(sorry about that).
And, you're right. You can approximate the data to y=a*x^b but what I need ( and still need )are the constants.
They would be different.
What I need now is way of simplifying the function y(x)=a*[cosh(b*(c-x))/cosh(d)] to y(x)=a*cosh(x)+b.
by the way, if you still need the sample. Here it go the right one:
x = [.02,.04,.055,.075,.095,.115,.145,.175, .215,.255,.305,.36,.46,.56,.71,.86,.94]

y =
[89.62, 83.03, 76.37, 67.38, 62.83, 58.25, 53.64, 46.64, 39.57, 32.40, 27.56, 22.69, 15.28, 10.27, 5.22, 2.67, 2.67]
   
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
Sorry, do not understand "...What I need now is way of simplifying the function y(x)=a*[cosh(b*(c-x))/cosh(d)] to y(x)=a*cosh(x)+b. ..."

I take it that you are looking for a regression of a given type and all you want is a way how to determine the coefficients for any given dataset.

0
 
LVL 13

Accepted Solution

by:
cri earned 50 total points
Comment Utility
The best approximation of the last dataset _I_ am able to do with _Excel_ _97_ is an exponentional trendline of the form y = 93.409 * EXP^(-3.9686*x). The error is <= +-5% except for the last two data points which you indicate having the same y value (i.e. 2.67, ?), where the error is -15.2% and +16.1%.

I do not _think_ that a regression in form of y=a*cosh(x)+b will give you a good fit. Example: If you take a curve which passes the endpoints exactly i.e. -183.01*cosh(x)+272.67 you will see that the slope form is convex instead of concave. I do not know Maple at all, see whether it has curve fitting with arbitrary functions.  

If Mathcad (7) is an option, see what you can do with the 'genfit' function. Perhaps calacuccias simplification of your original model could result in a better fit than the exponential fit gained with Excel. Attention: Will require a brush up in partial derivatives...

However, should you want it in Excel, then calacuccia or I can show you how to automate the _retrieval_ of the coefficients. Keywords: Logest function (see help) is the quickest, I personnally would prefer fetching the coefficients displayed in the trendline by VBA/Excel, as you get a visual check you do not get with Logest (unless defining an own diagram for this).

PS: Some feedback would be appreciated or did the problem solve itself in the meantime ?
0
 

Author Comment

by:calab
Comment Utility
I got it
Thank you for helping me out.
0
 

Author Comment

by:calab
Comment Utility
thanks
0
 
LVL 13

Expert Comment

by:cri
Comment Utility
You are welcome.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now