logarithmic equation: predicting values on a curve

yoohoo

New Member
Joined
May 27, 2004
Messages
9
Hi,

I am trying to predict values on a logarithmic curve based on a few known coordinates.


For example, if I had the following dataset, I would like to predict any of the values in between:
1=1
10=15
25=32
50=55
75=78
100=100

I tried calculating a y=b*ln(x)+c equation with little luck. Any ideas to help guide my efforts?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You are talking about a "best fit" for a given data set. The most "scientific" way to calculate this "best fit" curve is based on the Least Squares Method (L.S.M.). Probably the best way to get acquainted with this method is to go to Google and do a search for either "best fit" or "Least Squares Method".

I can tell you this, though. Any predicton is just a guess! If you are happy with a guess, then the L.S.M. will help you to get a recognized result. But, remember, it is still just a "guess".

For instance, I had a friend who wanted to get compass readings in his boat and actual directions, and do a L.S.M. fitting to get a curve. I worked on this in QuickBASIC, came up with a beautiful program that lets you see all the data on an x-y graph, as well as any curve you choose, be it a straight line, a 2nd degree curve, a 3rd degree curve, etc., up to an nth degree curve, where n = number of data, up to 24, all based on the best fit curve, as predicted by the L.S.M. Well, he wasn't happy at all, because the only curve that passed through all points was the nth degree curve, and it was a very jumpy cure indeed, one that gave you no real predictablity at all!

In fact, a hand-drawn curve usually does a better fit than a L.S.M. fit, in my experience.

If you want to pursue this further, do e-mail me at:
RAEsquivelC@Yahoo.com

Regards,

Ralph
 
Upvote 0
O.K., I used my QuickBASIC Mathematics program, Curves4.qb, and got this "best fit curve": LOG10(y) = LOG10(x) + 0.069. Plot your values, and plot this curve, and tell us how you like it...
 
Upvote 0
Hi!
Its hard to make a very good curve to fit for random data.
One best curve that will fit any given data is a spline.
this algorithm gives good accuracy on values with in your given data. extrapolating is another story(guessing values outside your give data. But spline method of interpolation is far better than the LSM. I ahve posted a code sometime ago. If you are interested see link bellow.
http://www.mrexcel.com/board2/viewtopic.php?t=77570&start=10
 
Upvote 0
Well, I went ahead and used the Excel charting system, and got, for a straight-line fit: y = 0.9773x + 4.3197, almost y = x + 4.3197. I don't think you will find a better-fitting curve!
 
Upvote 0
Hi,

Thanks Sixth Sense and RalphA for help guiding my efforts and pointing me in the right direction.

I don't know if it's been discussed on this forum, but I ran across this GREAT addon for excel, which includes a spline function:

http://www.xlxtrfun.com/XlXtrFun/XlXtrFun.htm

So far, I must say I am very impressed with spline and the XlXtrFun implementation. Incredible comes to mind. In the sample data set the curve could not be more perfect.

The only caveat, and i believe it applies to all polynomial curves, is with forecasting. It's so sensitive, that the last few knots can heavily influence the projection, so in this regard, spline seems best for interpolation. For extrpolation of the datasets I've been using, a linear trend is more accuate. Having said that, spline will likely become a valuable part of my repertoire.

Thanks again everyone!! :)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top