Identifying multiple Peaks and trough in the data set of % utilization values ranging between 1 - 100

Question

Dear VBA and Excel Gurus,

I have a series of data in one column of excel sheet and i want to identify peaks and troughs in that data set having % utilization values that are ranging between 1 - 100,

The nature of data is such that once a peak is achieved it will head downwards to a trough point and then again head upward for a peak and so on.

Can we identify multiple peaks and trough in the data set using any excel formula or vba code

Answer 1

Peaks:

=LET(
   data; B3:B50;
   (OFFSET(data;1;0)<data) * (OFFSET(data;-1;0)<data))

Troughs:

=LET(
   data; B3:B50;
   (OFFSET(data;1;0)>data) * (OFFSET(data;-1;0)>data))

Data is the range of numbers except first and last one. I suppose it's something like B3:B50 in your case. So put the formula in C3 and look for 1's along the column C or select B3:B50 and apply the next formula for conditional formatting:

=AND(OFFSET(B3;1;0)>B3);OFFSET(B3;-1;0)>B3))