This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the workbook lets you change how these moving averages are calculated. There is a download link below.
Above is an animated gif demonstrating a stock chart of S&P 500 with monthly prices. Two moving averages #1, #2, buy and sell points are plotted in this chart.
When the moving averages intersects a buy or sell point is created. If you use the value 7 in cell C1, moving average #1 uses the average of 7 months.
As you can see using a 15 month average (#1) returns better and fewer buy and sell points.
Extracting buy and sell points
Not only is the chart refreshed as you type new values in cell C1 and C2, the data below the chart is also instantly refreshed.
Make sure you see the prompt in the cell, double click on the cell if you don't.
Press and hold Ctrl+ Shift simultaneously
Press Enter once
Release all keys
How to copy array formula
Select cell B25
Copy cell (not formula in formula bar)
Select cell range B26:B50
Explaining formula in cell B25
Step 1 - Remove errors
The IFERROR function removes errors and returns an array containing values or nothing, errors are filtered out.
Step 2 - Check if cell is empty
The less than and greater than sign combined means "not equal to", in this case the logical operators make sure that the value is not equal to nothing. It returns boolean values TRUE or FALSE.
Step 3 - Check column N as well
Actions described in step 1 and 2 are also applied to column N.
Step 4 - If cells are not empty return the corresponding relative row number
The + sign between the logical expressions performs an OR calculation meaning if the cell in column M is not equal to nothing OR if the cell in column N is not equal to nothing then return TRUE, both logical expressions must return FALSE in order to return a FALSE.