Excel Momentum Formula
We have received a number of requests relating to an Excel momentum formula i.e. some sort of a count of how long a certain trend exists and then restarting the count once the trend changes.
A good example of this is the stock market. How long is the trend positive versus how long it is negative. A way to approach this is by aggregating the data onto a single sheet in rows or columns in date order with the movement shown as a positive or negative percentage.
You can then build a formula that incrementally counts while the signs on the numbers are the same, but when it changes it must restart at 1.
A useful function for this is the SIGN function which shows what the sign on a number is in Excel.
Below is an example of identifying the trend with the Rand vs US Dollar exchange rate