Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
Register  \/ 


How to count number of peaks in a column of data in Excel?

Supposing, two columns of data locates in your worksheet as the left screenshot shown, how to count the number of peaks in column B? Actually you can judge a cell value (such as B3) as a peak if it is simultaneously greater than B2 and B4. Otherwise, it is not a peak if it does not meet these two criteria. This article is talking about listing and counting all peaks in a column of data in Excel.

Count number of peaks in a column of data in Excel

Mark all peaks in a scatter chart

Count number of peaks in a column of data in Excel

The following formula can help you to count a number of peaks in a column of data directly in Excel.

1. Select a blank cell for placing the result, enter formula =SUMPRODUCT(--(B3:B17>B2:B16),--(B3:B17>B4:B18)) into the Formula Bar, then press the Enter key. See screenshot:

Note: In the formula, B3:B17 is the range from the third cell (including header cell) to the second last one of the list, B2:B16 is the range from the second cell (including header cell) to the antepenultimate one of the list, and finally B4:B18 is the range scope from the fourth cell (including header cell) to the last one of the list. Please change them as you need.

Mark all peaks in a scatter chart

Besides, you can easily figure out peaks of a column by creating a scatter chart and marking all peaks ion the chart. Please do as follows.

1. Select the cell - C3 which is adjacent to cell B3 (the second cell value of your list excluding the header), enter formula =IF(AND(B3>B2,B3>B4), "Peak","") into the Formula Bar and press the Enter key. Then drag the Fill Handle down to mark all peaks as below screenshot shown.

2. Select the column x and y, and click Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter with Straight Lines and Markers to insert a scatter chart into the worksheet. See screenshot:

3. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

4. In the Microsoft Visual Basic for Applications window, please click Insert > Module. Then copy and paste below VBA code into the Code window.

VBA code: Mark all peaks in a scatter chart

Sub CustomLabels()
    Dim xCount As Long, I As Long
    Dim xRg As Range, xCell As Range
    Dim xChar As ChartObject
    Dim xCharPoint As Point
    On Error Resume Next
    Set xRg = Range("C1")
    Set xChar = ActiveSheet.ChartObjects("Chart 1")
    If xChar Is Nothing Then Exit Sub
    xCount = ActiveChart.SeriesCollection(1).Points.Count
    For I = 1 To xCount
        Set xCell = xRg(1).Offset(I, 0)
        If xCell.Value <> "" Then
            Set xCharPoint = ActiveChart.SeriesCollection(1).Points(I)
            xCharPoint.DataLabel.Text = xCell.Value
            xCharPoint.DataLabel.Left = xCharPoint.DataLabel.Left - 15
            xCharPoint.DataLabel.Top = xCharPoint.DataLabel.Top - 7
        End If
End Sub

Note: In the code, Chart 1 is the name of the created scatter chart, and “C1” is the first cell of the help column which contains the formula results you applied in step 1. Please change them based on your needs.

5. Press the F5 key to run the code. Then all peaks are marked on the scatter chart as below screenshot:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.