How do I apply an equation from a scatter chart?

(1) You wrote : This supposedly evaluates to 308909 when x is 1.

It's a bit surprising because, as far as I know, LN(1) = 0 so, Y = 307025 !

Check in : http://en.wikipedia.org/wiki/Logarithm

(2) Use exactly the given formula.

Put values(x) in cells of column B

and

put the formula:

=238113*LN(B)+307025

in cells of column C

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 13:48:34

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Jesse Wonder Clark wrote:

Thanks, Yvan.

(1) The Wikipedia page is not much help to me because I barely understand any of this.

(2) I just want to type in a formula and have it come out the way it says it should.

(3 )If the formula I typed to you comes out the way you say, then it seems like Numbers is broken.

(4) I don't understand why you have two values in your column B. What does something like "2,4" mean in this case? Which number does the formula put in place of x?

(5) Maybe there's some easier way. The numbers I want to project from are:

308,909

466,969

571,839

I want to project the next six numbers in the series. How do I do this?

(1) If you don't understand what is written in the pointed page, better stop trying to use Neperian Logarithm !

(2) Thev formula behave as it must do

(3) Numbers behave as it must do.

You wrote that your formula must be :

y = 238113ln(x) + 307025

which reads :

y equal (the product of 238113 by the neperian logarithm of x) + 307025

When x =1, neperian logarithm of x =0

so the formula become (product of 238113 by 0) + 307025

as far as I remember, (product of 238113 by 0) equal 0

so your formula returns 307025

(4) I'm not English but French so on my machine, 15 divided by 2 isn't displayed as 7.5 but as 7,5

So on your machine, if you were fair enough to try to apply the posted formula, you would got :

(5) to 'project' numbers starting from given values, we muss have couples of values !

which is the x value associated to the y value 308909 ?

which is the x value associated to the y value 466969 ?

which is the x value associated to the y value 571839 ?

I apologize but trying to work with neperian logarithm and trendlines requires some knowledge.

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 15:31:00

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

<Edited by Host>

When I look at the graph of these three numbers it does not look logarithmic-- to me the best fit is a polynomial. In order to predict the next three values you need to know at what "X" value(s). The delta X between the three samples is not the same so you would need to share at what X values you want to predict the Y value.

Hello Wayne

Are you sure that the given values are three couples of integer numbers ?

Given the range of values given at the beginning :

238113

307025

and the fact that the trendline is supposed to have y = 307025 when x= 1

I'm quite sure thare they are three y values

x = 1 y = 307025

x = ? y = 308909

x = ? y = 466969

x = ? y = 571839

I was able to build a table of values matching the given equation and the three given y values :

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 18:52:21

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

Yvan,

Haha... I am so silly. I looked at the numbers Jesse posted:

as coordinate pairs and should have looked at them as Y values (with a thousands separator). Either way Jesse still needs to indicate the list of X values to calc Y at. But those are easy to calculate using the formula from the curve fit equation "=23813*LN(X) +307025"

It's what I did

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 19:52:11

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

<Edited by Host>

Jesse Wonder Clark,

By definition anything raised to the 0 is 1. That is...

10 ^ 0 = 1 (10 raised to the 1), e ^ 0 is 1

conversely:

log returns the exponent to which 10 is raised... so...

log (10 ^ 0) = log (1) = 0

LN (or natural log, also known as "neperian" log, as Yvan pointed out) returns the exponent to which e is raised to:

LN(e ^ 0) = LN(1) = 0

Therefore some of you confusion originates from the statement:

The equation is: y = 238113ln(x) + 307025

This supposedly evaluates to 308909 when x is 1.

Since you misunderstood how LN works you expected a different result when x = 1.

What you SHOULD have expected was:

when x = 1, Y(x) = 238113 * LN(x) + 307025 ->

Y(1) = 238113 * LN(1) + 307025

Y(1) = 238113 * 0 + 307025

Y(1) = 0 + 307025

Y(1) = 307025

I hope this helps

Hi Wayne

It's what I wrote but, maybe the same thing described with other words may be understood 😉

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 20:59:48

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

I feel that I understand where is the real problem.

The OP seems to misunderstood what is a trendline.

It's not a perfect representation of datas, it's an approximation.

When I built the curve representing the given equation, I got these three points descriptors:

If I replace this set by this edited one:

the calculated trendline become :

y = 236327 LN(x) + 308715

which don't pass on the point x=1, y=308909

Everybody aware of what is a trendline will not be surprised but the OP is surprised.

Given that, I decided to drop the intermediate point and kept only

This time the returned equation is :

y = 236419 LN(x) + 308909.

Using this equation to calculate the y value for x = 1.95759111 I got : 467 715.115735082

Using this equation to calculate the x value giving y = 466 969.000423094 , I got 1,9514228816

So we may assume that the measured values are somewhere in the range ;

x = 1,9514228816 y = 466 969.000423094

and

x = 1.95759111 y = 467 715.115735082

Which is a small range : 16/10000

To the OP's attention, I add that a trendline equation must be accompanied of the range of uncertainty.

If the point x=0 y = 308909 is a guaranteed one, we may say that the trendline equation is

y = 308909 + (ln(x) * (236419± epsilon))

Of course, to build this trendline, I choose the points whose distance is the maximum one to reduce the uncertainty.

I apologize for the OP, but he is unable to teach to an old ape like me which studied statistics to be able to analyze the behavior of structures made of concrete the way to work with trendiness 😉 Of course, I applied shortcuts which would be reproved by my old teachers but the global idea is here.

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 21:53:06

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

As I wrote in my late post, a trendline is an approximative representation of a function.

The one calculated by Numbers is perfectly calculated as it assumed that the x values associated to the given ones are 1, 2, 3

Alas for you, a logarithmic trendline can't exactly join your three points.

This is why I decided to build a trendline using only the first and the last values.

In my late post I did that with values calculated from your equation.

I repeat the game with the x values 1, 2 and 3

Using only these two points (cells with yellow background), Numbers built the best logarithmic trendline which may be offered.

In the column C (green background) I used these trendline equation to calculate y values.

As you may see, the first value matches perfectly the original one, the value for x =2 is exact with a tolerance of 16/1000

the third point is exact with a tolerance of -38/100 000 000

For physical measures it would be considered as a correct result.

As you see, the values aren't matching exactly your initial ones.

The chart at the bottom was built with the range A1…B3

I didn't asked for a logarithmic trendline but for a polynomial one .

Calculating the y value with this equation match perfectly the original values.

This doesn't prove that the second scheme is better than the first one.

I don't know what you want to achieve starting from these datas.

If you want to make previsions for years 2011 and 2012 for instance,

the logarithmic curve will return 640689 for 2011 and 694094 for 2012

the polynomial one will return 623519 for 2011 and 622009 for 2012

It's not surprising because the polynomial curve has a local maximum value and a local minimum one.

Clearly, the local maximum one is reached during year 2012.

But nothing allow us to say that one is better than the other one because nobody knows the true statistical behaving of customers.

I have a little preference for the logarithmic trendline but can't be more precise.

Honestly, at a two years horizon, we may say that they give the same results (the difference is inferior to 12% for year 2012)

If the goal is to have an idea of the hardware expansion to program, I would make my choices according to logarithmic

If the goal is to have an idea of the received cash in 2012, I would work upon the polynomial basis.

No, I'm not issued from Normandy 😉

PS, if you don't understand everything at first reading, take a breath and read a second time.

I will switch off in a few minutes so I will not be back until tomorrow.

Yvan KOENIG (VALLAURIS, France) mardi 28 juin 2011 23:15:32

iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8

Please : Search for questions similar to your own before submitting them to the community

To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !

<Edited by Host>

Jesse Wonder Clark wrote:

I have a scatter chart with three points of data. I've created a logarithmic trendline. I clicked the option to show the equation from the chart.

The equation is: y = 238113ln(x) + 307025

But how the heck do I type that equation into a Numbers cell? What do I actually type in the cell to turn 1 into 308909?

I just want to use that formula to project my next numbers.

Hi Jesse,

Going back to your original questions...

You don't. The calculated trendline is a 'best fit curve' for the given data. Leave it as is. Plot your actual data, and don't connect those data points.

Calculate only the projected data points for x=4, 5 and 6, using the formula generated by Numbers.

Coded for a Numbers table, where the x values are in column B, the formula is:

= 238113*LN(B) + 307025

Place the formula in C5 and fill it down into C6 and C7 (these are the three rows labelled "Projected")

In the table, x values are entered in column B. The first three Y values are the original data, entered directly into the cells. The last three are calculated, using the formula above.

Select cells B2-C7, then make the chart.

OR, if you're adding these three rows to your original data table, click on the chart to select it, then click on the Fill handle at the bottom right of the cells selected to make the chart and drag it down to add the three rows of calculated data.

Best practice would dictate an indication on the chart of which data is projected. Here's one way of doing that—placing a semi transparent coloured mask over the projected portion of the chart. Here the rectangle has opacity set as 27%. The word "Projected" is in a separate text box.

Regards,

Barry