PROBLEM:
Evaluate a range of cells in two columns of data flowing into excel-style spreadsheet. (Excel functions all work in this environment.)
Attributes:
Data is updating constantly, all previous data is pushed downward as new cell rows are printed - the most recent data is in row 3. Rows 1 and 2 are column labels
Columns BR and BZ (Green columns - see examples below) each display occurrences of 2 different length Moving Average (MA) crossovers (total of 4 MAs).
Columns BS and CA (Red columns) show occurrences of the same MAs as above crossing in opposite direction.
All cells contain 0 unless a MA crossover occurs.
Non-zero cells contain value of the MA at the point of crossover.
Crossovers (non-zeros) are always positive numbers.
I WANT TO MAKE THESE CALCULATIONS USING CELL FORMULAS/FUNCTIONS:
If a MA crossover (non-zero) appears in BR3 (topmost data cell) compare this value to the last (most recent/nearest to the top) crossover (non-zero) in both Columns BR and BZ.
1. Is the value in BR3 > or < that of the next most recent crossover (non-zero) in BR or BZ
2. How many cells back is the previous crossover (non-zero) in BR or BZ?
If a crossover (non-zero) appears in BZ3 (topmost data cell) compare this value to the last (most recent/nearest to the top) crossover (non-zero) in both Columns BR and BZ.
3. Is the value in BZ3 > or < the next most recent crossover (non-zero)
4. How many cells back is the previous crossover (non-zero) in BR or BZ?
5. Solve same calculations for combinations of cells in columns BS and CA.
Examples:
Is BR3 or BZ3>BR5 and how many cells back is BR5
Is BS3<BS14 and how many cells back is Bs14
Is BZ3>BR4 and how many cells back is BR4
Is CA3<BS17 and how many cells back is BS17
