# Calculate percentage change since last peak or trough (of variable periods)

#### gazza365

##### New Member
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

 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>

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### pjmorris

##### Well-known Member
Hi Gazza,

For me, the easiest solution is to use a helper column. In my version of your sheet I used column C:

In cell C2 enter: =IF(B2="",C1,A2)
In cell D3 enter: =IF(B2<>"",IFERROR((C2-C1)/C1,""),"")

The IFERROR is only to avoid an error in D3.

Then copy and paste both formula's down their respective columns.

HTH.

Last edited:

1,191,397
Messages
5,986,359
Members
440,020
Latest member
IfsandSums

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back