# Counting number of peaks in graph

#### evydmb

##### New Member
Hi,
I have a large set of data which consists of only 0s and 1s, When plotting on a graph, the 1s represent [FONT=inherit !important][FONT=inherit !important]peaks[/FONT][/FONT], but a single peak spans over a number of data points, and sometimes briefly drops to 0 but then continues. I cant figure out which function to use to count the number of peaks in my data, given that even if it drops to zero for a certain specified time frame, it still counts as one peak. Any help would be appreciated. Hope the example below helps:
Here, the time frame for 0s between two peaks to still count as one peak is 1 second

Heres a visualisation of the actual problem

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Andrew Poulsom

##### MrExcel MVP
Try in C2 copied down:

=IF(B2=0,"",IF(SUM(B1:B2)=1,MAX(C\$1:C1)+1,C1))

The results won't be the same as yours, but I think they are correct.

#### evydmb

##### New Member
I came up with a similar function, but in the end that gives me the total number of peaks disregarding the small dips in one peak. The thing i cant get around is how to count a 1 second dip in a peak as one peak instead of 2.

#### circledchicken

##### Well-known Member
Hi,

I'm sure there are better ways, but one easy formula method to get you started might be:

• Ensure your data starts in row 3, with the column headers in row 2
• Add two helper columns - Peak start (column C), Peak end (column D), Peak number (column E).
• Drag down the following formulae adjusting the ranges as appropriate (e.g. change the 29 in the last formula to the actual last row of your dataset):
C3:
Code:
``````=IF(
AND(B3 = 1, COUNTIF(B1:B2, "<>1") = 2),
MAX(C\$2:C2) + 1,
"")``````
D3:
Code:
``````=IF(
AND(B3 = 1, COUNTIF(B4:B5, "<>1") = 2),
MAX(D\$2:D2) + 1,
"")``````
E3:
Code:
``````=IF(
MAX(C\$3:C3) = MIN(D3:D\$[B]29[/B]),
MAX(C\$3:C3),
"")``````

#### Andrew Poulsom

##### MrExcel MVP
If you can insert a row above row 1:

=IF(OR(SUM(B2:B3)=0,SUM(B3:B4)=0),"",IF(AND(SUM(B1:B2)=0,SUM(B2:B3)=1),MAX(C\$2:C2)+1,C2))

#### circledchicken

##### Well-known Member
Another single column alternative in C3 and drag down might be (assuming again the data starts in row 3):

Code:
``````=IF(
AND(B3 = 1, SUM(B1:B2) = 0),
MAX(C\$2:C2) + 1,
IF(
AND(B3 = 1, SUM(B4:B5) = 0),
MAX(C\$2:C2),
IF(
SUM(B3:B4) > 0,
C2,
"")))``````

The first IF condition finds a peak start, the second identifies a peak end and the last identifies the points in-between.

#### evydmb

##### New Member
I'm not sure if I'm doing something wrong, but applying the function in c3 does not give me anything (I did move all the data down to the 3rd row). HOWEVER, the finction for D3 seems to do the trick, gives the exactly what I wanted, not sure what was the purpose of C3 and E3. So thanks for that!
Ive got one more question though, if lets say i wanted to make a dip in the peak (to still count as one peak) last 4 seconds (instead of the earlier 1 sec), how would the formula change then?, i tried changing it to the following, the it doesnt give me the answer at the peak end, but rather at one row above the peak end.
=IF(AND(B3 = 1, COUNTIF(B4:B8, "<>1") = 4), MAX(D\$2:D2) + 1, "")</pre>

#### circledchicken

##### Well-known Member
I'm not sure what you mean by C3 not giving you anything - did you drag it all the way down to the end of your dataset? D3 would only have given you the peak ends so it shouldn't have worked alone based on what you wanted...

Anyway, for your next question, and ignoring the helper columns, if you want to change the dips one way is move all your data further down e.g. start in row 10 (with the column headers in row 9) and try the following in C10 and drag down:

Code:
``````=IF(
AND(B10 = 1, SUM(B[COLOR=#800000][B]5[/B][/COLOR]:B9) = 0),
MAX(C\$9:C9) + 1,
IF(
AND(B10 = 1, SUM(B11:B[B][COLOR=#800000]15[/COLOR][/B]) = 0),
MAX(C\$9:C9),
IF(
SUM(B10:B[B][COLOR=#800000]14[/COLOR][/B]) > 0,
C9,
"")))``````
The numbers in red are the ones that need to be adjusted depending on the number of dips. The example above shows it for 4 dips.
I think you can also adjust Andrew Poulsom's shorter formula in a similar way to get the same result.

Replies
2
Views
166
Replies
6
Views
634
Replies
5
Views
157
Replies
3
Views
897
Replies
11
Views
874

1,195,988
Messages
6,012,714
Members
441,722
Latest member
tpaman1975

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