Sumproduct

[rwolfgang]

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]

Welcome to the board.

Maybe:

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

 

[68Hybrid]

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]

Welcome to the forum.

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

	A	B	C	D
"Peaks"	Range

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"][/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]386[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]776[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]702[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=SUMPRODUCT(--(A6:A1004>A5:A1003),--(A6:A1004< A7:A1005))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=SUMPRODUCT(--(A6:A1004>A5:A1003),--(A5:A1003>A4:A1002),--(A6:A1004< A7:A1005),--(A7:A1005< A8:A1006))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




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.

 

[Eric W]

Out of curiosity, I wrote up a bare bones version of what I described:

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.