Looking for the fourth point value in y=ax+b

[DavidRoger]

This post is continue from my previous post. You may refer to the link below:

In my previous post, I tried to finish the value of the crossing point. In this post, I try to find the value of the fourth point when the two lines come to the same point. This mean 3 defined point and 1 non-defined point.

[MrShorty]

Can you clarify the question? I'm not sure I see the problem. Using linear interpolation, I see two points of intersection there, both of which are found by the current algorithm used in jhren's last spreadsheet in the thread. Do you need to choose between one of those solutions and how would you choose which is correct, or is there some other question about the given solution?

[DavidRoger]

DavidRoger(2).xls

Can you clarify the question? I'm not sure I see the problem. Using linear interpolation, I see two points of intersection there, both of which are found by the current algorithm used in jhren's last spreadsheet in the thread. Do you need to choose between one of those solutions and how would you choose which is correct, or is there some other question about the given solution?

I think I should clarify my question. This question has nothing to do with previous question but I am using the previous scenario to ask different question. It is better if you refer to my attachment.

[MrShorty]

Am I understanding correctly. You want to find the value in B57 that would make the two moving averages in C57 and D57 the same?

The easiest, if you don't want to change any cell formulas, might be to use Goal Seek/Solver. Add a formula =C57-D57. Then call Solver and tell it to set this cell to a value of 0 by changing B57.

To get something more automated, I would probably spend some time with the algebra:
sma1=average(5 cells)=sum(5 cells)/5=(x6+x7+x8+x9+x10)/5
sma2=average(10 cells)=sum(10 cells)/10=(x1+x2+...+x9+x10)/10
sma1=sma2 --> (x6+x7+...+x10)/5=(x1+x2+...+x10)/10 --> Solve this equation for x10. Then place this formula into B57.

[DavidRoger]

Am I understanding correctly. You want to find the value in B57 that would make the two moving averages in C57 and D57 the same?

The easiest, if you don't want to change any cell formulas, might be to use Goal Seek/Solver. Add a formula =C57-D57. Then call Solver and tell it to set this cell to a value of 0 by changing B57.

To get something more automated, I would probably spend some time with the algebra:
sma1=average(5 cells)=sum(5 cells)/5=(x6+x7+x8+x9+x10)/5
sma2=average(10 cells)=sum(10 cells)/10=(x1+x2+...+x9+x10)/10
sma1=sma2 --> (x6+x7+...+x10)/5=(x1+x2+...+x10)/10 --> Solve this equation for x10. Then place this formula into B57.

Do you mind telling more about the Solver?

[DavidRoger]

Am I understanding correctly. You want to find the value in B57 that would make the two moving averages in C57 and D57 the same?

The easiest, if you don't want to change any cell formulas, might be to use Goal Seek/Solver. Add a formula =C57-D57. Then call Solver and tell it to set this cell to a value of 0 by changing B57.

To get something more automated, I would probably spend some time with the algebra:
sma1=average(5 cells)=sum(5 cells)/5=(x6+x7+x8+x9+x10)/5
sma2=average(10 cells)=sum(10 cells)/10=(x1+x2+...+x9+x10)/10
sma1=sma2 --> (x6+x7+...+x10)/5=(x1+x2+...+x10)/10 --> Solve this equation for x10. Then place this formula into B57.

Do you mind telling more about the Solver?

[MrShorty]

Microsoft's introductory file (there should be a similar help file in your Excel installation) https://support.office.com/en-us/art...d-078f96d1652c

The introductory page from the programmer's of Excel's Solver: http://www.solver.com/excel-solver-help

Since Solver's default algorithm is a Newton-Raphson type algorithm, I like to recommend a page like this (http://www.cs.utah.edu/~zachary/isp/...ot/Newton.html ) to understand how the NR algorithm works when finding roots. The problem you have defined is essentially a root finding problem.

[DavidRoger]

Thank you for your help and guideline. Have a very happy day.