I have a series of values that goes like a sinus curve. I would like to find each top value in the curve. Is there a function for that?
In today's video, Bill uses AND, IF and FILTER to find the peaks from the data.
In today's video, Bill uses AND, IF and FILTER to find the peaks from the data.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2353.
How can we extract the peaks from a series of sine curves?
Boy, great question today from JC.
He has a data series of sine curves, and he wants to extract the peak values of each curve.
I have a solution for this, but there has to be a better way.
Here's the data that's being charted.
It's in this column D here, from D3 down to D something or other.
To look for the peaks, I'm going to leave that first cell blank because what I think I have to do is I have to look at this value and see if this value in this row is greater than the next value. That means that we're starting to head downhill.
But if you think about it like all of these values here, the next value is less than the current value. We have to do two things.
We have to check to see if this value is greater than this value, but we also have to check to see if this value is more than the previous value.
Because we have two things to check for inside the F statement we're using.
And function, so if the blue cell is greater than the red cell, if the blue cell is greater than the earlier cell.
If both of those are true, then bring that value over so that's the peak.
Otherwise, " " double click to copy that formula down and we'll see that there's a peak here.
Then I'm going to hit page down. There's a peak there at 0.9.
Hit page down, peak there, and a peak there.
Of course, it's a real pain to have all those different peaks.
If you're in Microsoft 365 or Office 2021, then you have this great function here that will extract all of those values into one range, the filter function.
Look through all of these peak values, get it where E3 to E227 is not equal to a blank " ", and we'll get all of the peak values.
So that's a one, a 0.9, another one, 0.9, 0.5, 1.45.
That one, it looks like it was still heading up, but right now the peak is 1.20147.
All right, so that's it. Down in the YouTube comments, let me know.
I'm sure there's some awesome lambda function that'll just do this without all these helper cells, but for me, the helper cells are the way to get it done.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank JC for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
How can we extract the peaks from a series of sine curves?
Boy, great question today from JC.
He has a data series of sine curves, and he wants to extract the peak values of each curve.
I have a solution for this, but there has to be a better way.
Here's the data that's being charted.
It's in this column D here, from D3 down to D something or other.
To look for the peaks, I'm going to leave that first cell blank because what I think I have to do is I have to look at this value and see if this value in this row is greater than the next value. That means that we're starting to head downhill.
But if you think about it like all of these values here, the next value is less than the current value. We have to do two things.
We have to check to see if this value is greater than this value, but we also have to check to see if this value is more than the previous value.
Because we have two things to check for inside the F statement we're using.
And function, so if the blue cell is greater than the red cell, if the blue cell is greater than the earlier cell.
If both of those are true, then bring that value over so that's the peak.
Otherwise, " " double click to copy that formula down and we'll see that there's a peak here.
Then I'm going to hit page down. There's a peak there at 0.9.
Hit page down, peak there, and a peak there.
Of course, it's a real pain to have all those different peaks.
If you're in Microsoft 365 or Office 2021, then you have this great function here that will extract all of those values into one range, the filter function.
Look through all of these peak values, get it where E3 to E227 is not equal to a blank " ", and we'll get all of the peak values.
So that's a one, a 0.9, another one, 0.9, 0.5, 1.45.
That one, it looks like it was still heading up, but right now the peak is 1.20147.
All right, so that's it. Down in the YouTube comments, let me know.
I'm sure there's some awesome lambda function that'll just do this without all these helper cells, but for me, the helper cells are the way to get it done.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
I want to thank JC for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
