Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Fitting a Model to a set of data

Posted on 2000-04-25
Medium Priority
301 Views
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
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
• 7
• 6

LVL 13

Expert Comment

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

ID: 2749334
I doesn't have to be Excel. I'm not familiar with MathCad, though. What about Matlab?
I've just signed in. So, I still got 25 points left.

0

LVL 13

Expert Comment

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

Author Comment

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

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

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

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

ID: 2766648

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

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

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

cri earned 200 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

ID: 2784732
I got it
Thank you for helping me out.
0

Author Comment

ID: 2784735
thanks
0

LVL 13

Expert Comment

ID: 2785265
You are welcome.
0

## Featured Post

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are â€¦
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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â€¦
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
###### Suggested Courses
Course of the Month6 days, 20 hours left to enroll