Sumproduct

rwolfgang

New Member
Joined
Jun 14, 2018
Messages
1
Hello,

I have a large set of data as a function of time, and I am trying to figure out the number of peaks in the data. However, there is some noise in the data that I would like the formula not to count as peaks by entering a certain threshold that the peaks must be above to be counted peaks. The formula I have now it written below for B1:B36:

=SUMPRODUCT(--(B2:B35>B1:34),--(B2:B35>B3:B36))

But I want to exclude peaks below a certain height from the product. Is there a way to do this? I was thinking of maybe using an IF(AND... function to accomplish this, but I can't seem to figure it out. Thanks in advance for the help!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Welcome to the board.

Maybe:

=SUMPRODUCT(--(B2:B35>B1:B34),--(B2:B35>B3:B36),--(B2:B35>5))
 

68Hybrid

New Member
Joined
Oct 1, 2018
Messages
1
Hello,

I have a large set of data as a function of time, and I am trying to figure out the number of peaks in the data. However, there is some noise in the data that I would like the formula not to count as peaks by entering a certain threshold that the peaks must be above to be counted peaks. The formula I have now it written below for B1:B36:

=SUMPRODUCT(--(B2:B35>B1:34),--(B2:B35>B3:B36))

But I want to exclude peaks below a certain height from the product. Is there a way to do this? I was thinking of maybe using an IF(AND... function to accomplish this, but I can't seem to figure it out. Thanks in advance for the help!
Hello,

I have a very similar question:
I need to determine the number of cycles completed based on atemperature log of a system showing internal temperatures as the system ispowered on and off. The system is on /off long enough to reach thermal equilibrium.My data ranges from ~ambient (22C) to somewhere between 60C and 80Cdepending upon load.
I’m using the following formula to start with:

=SUMPRODUCT(--(D12:D7607>D11:D7606),--(D12:D7607>D13:D7608),--(D12:D7607>70))

My understanding is that this shows me the number of peaksover 70C. However, given a set of valuessuch as the following, I capture more than the true peak:

70.0, 75.0, 77.2, 77.3, 77.4,77.3, 77.5, 77.6, 77.7, 77.8,77.7, 77.8, 77.6, 77.5, 76.0….

I believe this formula will return 3 peaks instead of the 1that I’m after.

I have around 51,500 data points to go through, so manuallygoing through this does not seem like a viable option. How can I modify or add to this formula tofilter out the minor fluctuations that are currently being counted as a “peak?”

Idea:
Is it possible to create some kind of IF statement or condition where we can say, “if the 5 values before CELL X trend positive AND the 5 values after trend negative, then perform the SUMPRODUCT formula?”

I wouldn’t have gotten this far if it wasn’t for all the time I spent reading through threads on this forum. Thanks for your help!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Welcome to the forum.

You can certainly add levels by adding conditions to the SUMPRODUCT like this:

ABCD
1
2
3
4"Peaks"Range
5
61901551
7386
867582
9398
1077613
11702
1238

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C6=SUMPRODUCT(--(A6:A1004>A5:A1003),--(A6:A1004< A7:A1005))
C8=SUMPRODUCT(--(A6:A1004>A5:A1003),--(A5:A1003>A4:A1002),--(A6:A1004< A7:A1005),--(A7:A1005< A8:A1006))
C10=SUMPRODUCT(--(A6:A1004>A5:A1003),--(A5:A1003>A4:A1002),--(A4:A1002>A3:A1001),--(A6:A1004< A7:A1005),--(A7:A1005< A8:A1006),--(A8:A1006< A9:A1007))

<tbody>
</tbody>

<tbody>
</tbody>




I tried coming up with a formula where you just enter the range you want (1-5) in a cell and the formula will adapt, but that's probably more trouble than it's worth. It's not too hard to add another pair of conditions.

What's more problematic is that I rather doubt that this will give you what you want. If there's any slight variation in the temperatures in a given range, it won't be counted. In the random sample I created, the number of peaks dropped rapidly. Your not-so-random range will probably behave differently, but I suspect you'll still miss out on some peaks.

What you'd probably want to do is some type of slope analysis. For example, take cells A1:A10, calculate the slope using SLOPE, and see if it is rising (+) or falling (-). Then repeat with A2:A11. At some point, the slope will switch from rising to falling, and that's where you want to count a peak.

That's probably more that I can help with, since it would require a lot of analysis: what's the ideal range size to use, how to handle outliers, etc. Also probably VBA vs. a formula.
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Out of curiosity, I wrote up a bare bones version of what I described:

Rich (BB code):
Sub test1()

    rng2 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    s1 = 0
    p = 0
    For r = 6 To 995
        Set Rng = Range("A" & r & ":A" & r + 9)
        s = WorksheetFunction.Slope(Rng, rng2)
        If s < 0 And s1 > 0 Then
            Debug.Print "row "; r; " may be a peak"
            p = p + 1
        End If
        s1 = s
    Next r
    Debug.Print "There are "; p; " potential peaks"
End Sub
The blue items refer to the location of your range, the red items indicate the size of the range to use for the slope. Results are just printed to the immediate window. To try it, open a copy of your workbook, press Alt-F11 to open the VBA editor. From the menu, select Insert > Module, then paste the above code into that window. Change the colored items as needed. Press F5 to run it, and check the results from the Immediate window. (Press Cntl-G if it's not visible.)

But this is very rudimentary, and may not give you the results you want. But it may be worth a look. Let me know.
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top