Hi,
I have a set of data in column A that increases and decreases randomly (e.g. stock market prices).
I have a formula in column B that determines where the peaks and troughs (local minima and maxima) are in the data, and labels them as such. The number of data points between the peaks and the troughs is variable (e.g. sometimes there are several increases before a downturn/upturn, sometimes there is only one or two).
I would like a formula in column C that calculates the percentage change in the data since the last peak or trough.
Thanks very much for any help in advance!
G
<tbody>
</tbody>
I have a set of data in column A that increases and decreases randomly (e.g. stock market prices).
I have a formula in column B that determines where the peaks and troughs (local minima and maxima) are in the data, and labels them as such. The number of data points between the peaks and the troughs is variable (e.g. sometimes there are several increases before a downturn/upturn, sometimes there is only one or two).
I would like a formula in column C that calculates the percentage change in the data since the last peak or trough.
Thanks very much for any help in advance!
G
Data | Peak/Trough | % change since last P/T | (In other words, to calculate) |
1.962 | Peak | ||
1.954 | |||
1.929 | Trough | -1.7% | % change A2 >> A4 |
1.933 | |||
1.963 | Peak | 1.8% | % change A4 >> A6 |
1.879 | |||
1.876 | |||
1.864 | Trough | -5.0% | % change A6 >> A9 |
1.889 | |||
1.902 | |||
1.954 | |||
1.989 | Peak | 6.7% | % change A9 >> A13 |
1.988 | Trough | -0.1% | % change A13 >> A14 |
2.010 | |||
2.046 | |||
2.105 | |||
2.144 | Peak | 7.8% | % change A14 >> A18 |
<tbody>
</tbody>