Solved

Fitting a Model to a set of data

Posted on 2000-04-25
14
284 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
ID: 2749241
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
ID: 2749334
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
ID: 2750592
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:calab
ID: 2752070
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
ID: 2752329
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
ID: 2755800
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
ID: 2756574
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
 
LVL 13

Expert Comment

by:cri
ID: 2766648
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
ID: 2774821
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
ID: 2775261
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
ID: 2778379
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
ID: 2784732
I got it
Thank you for helping me out.
0
 

Author Comment

by:calab
ID: 2784735
thanks
0
 
LVL 13

Expert Comment

by:cri
ID: 2785265
You are welcome.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

805 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