Hi,
I have data in column B from Cell B5 onward. I have timestamps in column A from A5 onward.
The graph has two lines, one is Blue and other one is Orange. Blue line comes out through the data in column E from E30 onward. Orange line comes out through the data in column F from F30 onward.
Now, Orange line is starting from time 0:33 (hh:mm) and crossing at 0:42. In Cell G5 I want this time stamp (0:33 to 0:42) for this crossover. Also, I want a difference of B38-B47 in cell H5. B38 is the respective value of A38 (0:33). And B47 is respective value of A47 (0:42). As, orange line is above of Blue for this crossover, so “Down” should be written in cell I5. In cell J5, crossover time should be written, which can be obtained by the difference of time in cell G5 which is 0:42-0:33=9 minutes.
At 0:42
Orange line is starting from time 0:42 and crossing at 0:59. In Cell G6 I want this time stamp (0:42 to 0:59) for this crossover. Also, I want a difference of B64-B47 in cell H6. B64 is the respective value of A64 (0:59). And B47 is respective value of A47 (0:42). As, orange line is below of Blue for this crossover, so “Up” should be written in cell I6. In cell J6, crossover time should be written, which can be obtained by the difference of time in cell G6 which is 0:59-0:42=17 minutes.
Please note that for time stamp of 0:33 to 0:42, there is a Down trend. So, difference is B38-B47 (smaller minus bigger cell number of column B).
For time stamp of 0:42 to 0:59, there is a Up trend. So, difference is B64-B47 (bigger minus smaller cell number of column B).
At 0:59
This crossover is for only 1 minute duration. At 0:59, orange line crosses blue line and it is Down trend. Again, orange line cross at 1:00. So, in cell G7, I want a time stamp of 0:59 to 1:00. I want a difference of B64-B65 in cell H7. B64 is the respective value of A64 (0:59). And B65 is respective value of A65 (1:00). As, orange line is Above of Blue for this crossover, so “Down” should be written in cell I7. In cell J7, crossover time should be written, which can be obtained by the difference of time in cell G7 which is 1:00-0:59=1 minutes (take 1:00 as 60).
In this way I want to get results of the complete chart.
(Help file is Attached for further clarification)
I have data in column B from Cell B5 onward. I have timestamps in column A from A5 onward.
The graph has two lines, one is Blue and other one is Orange. Blue line comes out through the data in column E from E30 onward. Orange line comes out through the data in column F from F30 onward.
Now, Orange line is starting from time 0:33 (hh:mm) and crossing at 0:42. In Cell G5 I want this time stamp (0:33 to 0:42) for this crossover. Also, I want a difference of B38-B47 in cell H5. B38 is the respective value of A38 (0:33). And B47 is respective value of A47 (0:42). As, orange line is above of Blue for this crossover, so “Down” should be written in cell I5. In cell J5, crossover time should be written, which can be obtained by the difference of time in cell G5 which is 0:42-0:33=9 minutes.
At 0:42
Orange line is starting from time 0:42 and crossing at 0:59. In Cell G6 I want this time stamp (0:42 to 0:59) for this crossover. Also, I want a difference of B64-B47 in cell H6. B64 is the respective value of A64 (0:59). And B47 is respective value of A47 (0:42). As, orange line is below of Blue for this crossover, so “Up” should be written in cell I6. In cell J6, crossover time should be written, which can be obtained by the difference of time in cell G6 which is 0:59-0:42=17 minutes.
Please note that for time stamp of 0:33 to 0:42, there is a Down trend. So, difference is B38-B47 (smaller minus bigger cell number of column B).
For time stamp of 0:42 to 0:59, there is a Up trend. So, difference is B64-B47 (bigger minus smaller cell number of column B).
At 0:59
This crossover is for only 1 minute duration. At 0:59, orange line crosses blue line and it is Down trend. Again, orange line cross at 1:00. So, in cell G7, I want a time stamp of 0:59 to 1:00. I want a difference of B64-B65 in cell H7. B64 is the respective value of A64 (0:59). And B65 is respective value of A65 (1:00). As, orange line is Above of Blue for this crossover, so “Down” should be written in cell I7. In cell J7, crossover time should be written, which can be obtained by the difference of time in cell G7 which is 1:00-0:59=1 minutes (take 1:00 as 60).
In this way I want to get results of the complete chart.
(Help file is Attached for further clarification)
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H5 | H5 | =B38-B47 |
| H6 | H6 | =B64-B47 |
| H7 | H7 | =B64-B65 |
| H8 | H8 | =B71-B65 |
| H9 | H9 | =B71-B98 |
| H10 | H10 | =B110-B98 |
| H11 | H11 | =B110-B117 |
| H12 | H12 | =B120-B117 |
| H13 | H13 | =B120-B126 |
| H14 | H14 | =B128-B126 |
| H15 | H15 | =B128-B134 |
| H16 | H16 | =B139-B134 |
| H17 | H17 | =B139-B140 |
| H18 | H18 | =B147-B140 |
| H19 | H19 | =B147-B149 |
| H20 | H20 | =B169-B149 |
| H21 | H21 | =B169-B183 |
| H22 | H22 | =B184-B183 |
| H23 | H23 | =B184-B190 |
| J5 | J5 | =42-33 |
| J6 | J6 | =59-42 |
| J7 | J7 | =60-59 |
| J8 | J8 | =66-60 |
| J9 | J9 | =93-66 |
| J10 | J10 | =105-93 |
| J11 | J11 | =112-105 |
| J12 | J12 | =115-112 |
| J13 | J13 | =121-115 |
| J14 | J14 | =123-121 |
| J15 | J15 | =129-123 |
| J16 | J16 | =134-129 |
| J17 | J17 | =135-134 |
| J18 | J18 | =142-135 |
| J19 | J19 | =144-142 |
| J20 | J20 | =164-144 |
| J21 | J21 | =178-164 |
| J22 | J22 | =179-178 |
| J23 | J23 | =185-179 |
| C16 | C16 | =AVERAGE(B5:B16) |
| C17:C153 | C17 | =(B17*(2/(12+1))+C16*(1-(2/(12+1)))) |
| D30 | D30 | =AVERAGE(B5:B30) |
| E30:E153 | E30 | =C30-D30 |
| D31:D153 | D31 | =B31*(2/(26+1)) + D30*(1-(2/(26+1))) |
| F38 | F38 | =AVERAGE(E30:E38) |
| F39:F153 | F39 | =(E39*(2/(9+1))+F38*(1-(2/(9+1)))) |
