X Estimation given an expected Y

[Alex]

Given a table such as this:

Month Data
1 3600
2 7200
3 10800
4 12000
5 13450
6 14100
7 13000
8 12340
9 11300
10 10390

how do I get Excel (XP/2002) to predict when, i.e. what month (if
ever) the data might touch 0?

Charting the graph and adding a trend line (with 5 forward points)
nicely illustrates the concept, but how do I get a calculated value?

Ps: I have displayed the equation of the trend line and used Goal Seek
to find the month values (0.3 and 12.89 where the 2 roots) but I don't
want to copy and paste the formula from the chart and run goal seek
everytime a new data point is added to the table above.

Thanks in advance for any help.

 

[doco]

Hmmmm...

Not sure what you are asking or what the regression intended is or needed
without more information. However, if I highlight the last two data points
(9 & 10) then drag the [.] in the lower right corner down the number goes to
380 in month 21 to -560 in month 22. If highlighting the entire data set
and drag down the numbers get progressively larger - quit in month 100. If
you try any series of rows in col B beginning with the peak (month 6) the
number reaches zero or less at about months 20 - 22 by using this drag down
process. Again, not sure what you are trying to do or why...

doco

 

[doco]

If I chart the data using an XY scatter plot and place a polynomial 4
trendline over the data then use the resulting formula for Y extended
forward of your data - the numbers get larger to 790,989 in month 27 where I
quit assuming the numbers will continue to be >0 ad infinitum.

Formula: y = 3.2547x^4 - 45.363x^3 - 240.01x^2 + 4819.5x - 971.67; R2 =
..9935

doco

 

[doco]

Hmmmm... I am not a statistician by any stretch but isn't X independent of
Y? And Y dependent on X?

doco

 

[Jerry W. Lewis]

You can use TREND() with Solver to determine the x that will produce a
given y. Give the appropriate form of the model (which you have not
stated), it might be possible to determin an analytical solution.

Jerry

 

[BenjieLop]

Any variable can be either "dependent" or "independent" as long as it i
properly defined.

The conventional equation has been

Y = f(x)

meaning "Y" is a function of "X" where "X" is the independent variabl
while "Y" is the dependent variable.

However, nobody can stop you from saying X=f(y) or A=f(b) or C=f(d) an
so on and so on ...

 

[Jerry W. Lewis]

While a 1:1 function can be inverted, so that y=f(x) would imply that
x=g(y), where g is the functional inverse of f, it becomes a bit more
dicey if you try to do the same thing in an estimation situation.

The rationale for least squares is based on the assumption that the
random uncertainty in the model comes into play solely through the
dependent variable. That is why calibration problems are usually
handled by estimating f() and then inverting that estimated function
rather than directly fitting g() from the data. Krutchkoff
(Technometrics 10:811-823, 1968) wrote a controversial paper that
contended that the biased estimator of x with from directly estimating
g() has smaller mean square error within the range of data than the
unbiased estimator from inverting an the estimate of f(). However, even
if you accept Krutchkoff's argument, the OP wanted to extrapolate beyond
the data, where it is unquestionably better to estimate f() and invert
that estimate.

Jerry

 

[Dana DeLouis]

I think, given what the op mentioned (..(0.3 and 12.89 where the 2 roots) )
that the op is assuming the data follows a 2-deg polynomial. If we make
that assumption, then for the op, here is one idea.

Select 3 horizontal cells, and array enter the following equation:

=LINEST(ys,xs^{1,2})

Where the x's and y's are your data.
The first cell is your x^2 term, and the third is the constant. Give all
three cells a range name like "a", "b", and "c_" to make it a little easier
to follow the equation.

Enter the following two equations:

=(-b + SQRT(b^2 - 4*a*c_))/(2*a)
=(-b - SQRT(b^2 - 4*a*c_))/(2*a)

I get:

-0.038118652
12.89001797

It looks like you may have copied the first answer incorrectly. (...(0.3
and 12.89 where the 2 roots) ...)


HTH