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

#### ramjet81

##### New Member
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.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### p45cal

##### Well-known Member
Ugh!
Would using Solver in the Analysis part of the Data tab be acceptable?

#### ramjet81

##### New Member
p45cal, do you know if solver can be used for that cell throughout the spreadsheet? What I mean is, I have 1,700 rows of data. I need to calculate this value for each day given the Y value for each of the two proceeding days. Or is Solver intended to only calculate one cell at a time? What I am trying to do is for every day in the spreadsheet take the prior 2 stock close prices to calculate the "threshold" price for any given day.

#### p45cal

##### Well-known Member
I suppose a bit of vba could be persuaded to do it for you, but see if the simple formula below, copied down gives you the right answer (69.36 is the same answer that solver gave me):
Excel Workbook
ABCDEF
1X'sY'sSlope >>-0.02
2368.8269.36
3270.12
4169.4
Sheet

#### ramjet81

##### New Member

p54cal,

Thanks for the formula. Can you explain your approach for coming to the formula in C2? That will help me understand if this is a long term solution or not.

Many Thanks.

#### p45cal

##### Well-known Member
Well, I looked at the Help in Excel for Slope where there's a formula showing how it determines slope. I re-arranged it after substituting the known slope and had your unknown value for the 3rd Y on its lonesome on one side of the equation and discovered that a lot of things could cancel out and be simplified. It assumes that the x values are equidistant from each other. The gradient of a best straight line through three points (with equidistant x values) is dependent only on the first and third points, the middle point can be any value, the slope remains the same. The slope of the best straight line is the same as the slope of a straight line joining the first and third points. It would not be so simple if there were more than three points to consider.

Last edited:

#### ramjet81

##### New Member

P54cal, What if the X's are not equidistant? Sounds like I need to take a similar approach as you did and see if I can "boil it down" to a simple formula. Thanks for your help...I will be back if I struggle.

#### shg

##### MrExcel MVP
The gradient of a best straight line through three points (with equidistant x values) is dependent only on the first and third points
That was a very clever observation. I would have stared at that result and wondered what I'd done wrong.

#### p45cal

##### Well-known Member
P54cal, What if the X's are not equidistant? Sounds like I need to take a similar approach as you did and see if I can "boil it down" to a simple formula. Thanks for your help...I will be back if I struggle.
Well, it was a condition that they were equidistant, so yes, it'd be significantly more complex if they weren't. If a worksheet formula got too involved, I'd consider going down a user-defined function route, probably one which would require your 1700 rows as an argument(s) and would be array-entered in the cells where you wanted to see the results so as to save on calculation (interim values would only need to be calculated once).

But can the threshold for today that you're calculating based on a single value 2 days ago and an arbitrary slope be useful? it seems to ignore price volatility.

That was a very clever observation. I would have stared at that result and wondered what I'd done wrong.
Thank you!

Last edited:

#### pgc01

##### MrExcel MVP
Hi

This is a solution for a more general case:

Given N points, you want to add a new point with a given X value and a target slope. What's the Y value for the new point?
In this example I start with a table in A2:A20, with slope 1.98.

I want to add a new point with X=17 (in A21) and a target slope of 2 for the whole A2:B21 table. The formula calculates the new Y at B21.

I then add yet a new point, with X=19 (in A22) and the same target slope. The formula calculates the new Y at B22.

This could go on. You just specify the X value for a new point and drag down the formula.

To make the formula simpler I defined the named ranges for the X values and the Y values

XVal =Sheet1!\$A\$2:INDEX(Sheet1!\$A:\$A,ROW()-1)
YVal =Sheet1!\$B\$2:INDEX(Sheet1!\$B:\$B,ROW()-1)

I also defined a named range for the target slope:

bTarget =Sheet1!\$F\$3

The formula in B21:

=(((ROWS(XVal)+1)*VAR.P(XVal)+(A21-AVERAGE(XVal))^2)*bTarget-(ROWS(XVal)+1)*COVARIANCE.P(XVal,YVal))/(A21-AVERAGE(XVal))+AVERAGE(YVal)

Copy down

Just to confirm the results I calculated the slopes for the tables. In F7:

=SLOPE(\$B\$2:\$B20,\$A\$2:\$A20)
Copy down

I seems to work, but I cannot garantee it, as I'm half asleep.

If you want to try it, this is the example I used:

Replies
0
Views
333
Replies
1
Views
105
Replies
3
Views
431
Replies
1
Views
2K
Replies
4
Views
302