Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Fitting a Model to a set of data

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
calab
Asked:
calab
  • 7
  • 6
1 Solution
 
criCommented:
Does it have to be Excel ? MathCAD or similar programs are better suited for this. If yes: How many points do you have ?
0
 
calabAuthor Commented:
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
 
criCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
calabAuthor Commented:
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
 
criCommented:
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
 
calabAuthor Commented:
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
 
calacucciaCommented:
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
 
criCommented:
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
 
calabAuthor Commented:
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
 
criCommented:
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
 
criCommented:
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
 
calabAuthor Commented:
I got it
Thank you for helping me out.
0
 
calabAuthor Commented:
thanks
0
 
criCommented:
You are welcome.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now