Hi,
Been doing this for awhile now. Search online doesn't help. Need to find formula for the following.
1) Formula to calculate minimum value (highlighted orange cell) for the crossing
2) Formula to calculate the value of the crossing (circled red in chart)
Please refer to the attachment for the data and chart.
Currently I use the average between point and the value is not very accurate.
Hope someone is good at this.
This should get you a lot closer on #2.
Let's see how this goes before we get into #1.
All function in column F is new to me. Need time to digest it.
Just to confirm. In column G, the formula is to detect the crossing of the sma?
Last edited by DavidRoger; 06-27-2015 at 02:41 AM.
Hi jhren,
I think I just solved formula #2. If it wasn't for the function in your formula,
I couldn't have found Linest. I don't know how to explain it but it just works.
Book_2.xls
Have you got any idea for formula #1?
Your calculation using LINEST is inaccurate. Increase the resolution of the chart at the intersection, i.e. set vertical axis to a range of 2 (e.g. 2190 to 2192 for the first) and add minor gridlines of .1 and you will see.
Here's a simpler method using basic graphing math based on two y=ax+b formulas—one for sma1 subset, another for sma2 subset—where the intersection must satisfy both equations. You won't see anything representing "x" in the column F formulas because the "x" subset is always {0,1} for your dataset... but the result values represent the "x" intersection value relative to that subset... which is why column E values being displayed are based on the column F value being 0 to 1.
Last edited by jhren; 06-27-2015 at 03:20 PM.
I've checked it by plotting a vertical line across the chart. It's like you said. Now I know why my third cross has two figure instead of one. This one is a lot better. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks