Solve for a specific "Y" variable given the "Slope" of the line.

[ramjet81]

I am using Excel to calculate thresholds for slope. X's are the number of a day. Y's are the price of a stock at the close of the day. Here is my Array:


X's Y's
3 68.82
2 70.12
1 69.40


The 3 day Slope of this array is -0.29 using the SLOPE function.


Now I want to replace 68.82 with a number such that my slope calculates to -0.02. Sure, I could simply manually iterate the 68.82 number to get it to equal -0.02, but I need a solution to mathematically solve the 68.82 number to achieve the -0.02 slope given the rest of the X's and Y's remain unchanged.


Thanks in advance!

 

[shg]

Well done, pgc!

 

[pgc01]

Thanks shg.

 

[pgc01]

I re-read the post #10 and realised that the example is too simple for the solution I posted.

The solution I posted is more general, it calculates the new Y value when I add a new point, given the X value and the target slope, any target slope.

In the example, however, all the points were added, given a new X value, but the same target slope.

This is a simpler problem.

In the case of the example I posted, I'd still use the formula I posted to calculate the Y for the first new value, in B21.

For the following new points, assuming, as in the example, that the target slope would always be the same (in the example = 2), in B22 and copied down, a much simpler formula:

=(A22-AVERAGE(XVal))*bTarget+AVERAGE(YVal)