What if analysis, can this be DONE??

[Barbara Harris]

Help me if anyone can!
I set up the function Trend(C1:C5).
From this the function returns the Linear regression value
for the latest data point C5. How can do a what if
analysis to find the value at C5 that equals the Linear regression value
i.e., solve for C5= Trend(C1:C5)?
Any way it can be done?

 

[Guest]

Per Help for the Trend function, if you omit the second argument, then C1:C5
are assumed to be the y-values corresponding to x-values of 1,2,...,5. If
you also omit the third argument, then it will give predicted values
corresponding to x-values of 1,2,...,5. If you only show one value from that
array of 5 values, then it will be the first value, not the last value; i.e.
Trend(C1:C5) as a single value will be the predicted value corresponding to
C1, not C5.

That said, the prediction line used by Trend(C1:C5) is the line with slope
and intercept given by LINEST(C1:C5). Per Help for Linest, the slope will be
the first output column, and the intercept will be the second output column.
Given the slope and intercept, any point on that prediction line satisfies
x=(y-intercept)/slope

Jerry