Solved

Fitting a Model to a set of data

Posted on 2000-04-25
14
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

737 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