Excel - find local maxima with multicell peak
I want to find a local maxima in a huge dataset with Excel and the way I've been trying is to compare the previous and the next value to make sure they are smaller eg.:
=IF(AND(C4>C3,C4>C5),"Local maxima","")
But the trouble with this formula is that if the peak stretches across multiple rows it won't catch that as local maxima. And that is despite the fact that it is a local maxima as values begins to fall.
(If you want to know why it is to find the local maxima in a set of spectrum data)
microsoft-excel
asked May 28 '14 at 16:01
Highace2
133113
add a comment |
I want to find a local maxima in a huge dataset with Excel and the way I've been trying is to compare the previous and the next value to make sure they are smaller eg.:
=IF(AND(C4>C3,C4>C5),"Local maxima","")
But the trouble with this formula is that if the peak stretches across multiple rows it won't catch that as local maxima. And that is despite the fact that it is a local maxima as values begins to fall.
(If you want to know why it is to find the local maxima in a set of spectrum data)
microsoft-excel
asked May 28 '14 at 16:01
Highace2
133113
add a comment |
I want to find a local maxima in a huge dataset with Excel and the way I've been trying is to compare the previous and the next value to make sure they are smaller eg.:
=IF(AND(C4>C3,C4>C5),"Local maxima","")
But the trouble with this formula is that if the peak stretches across multiple rows it won't catch that as local maxima. And that is despite the fact that it is a local maxima as values begins to fall.
(If you want to know why it is to find the local maxima in a set of spectrum data)
microsoft-excel
asked May 28 '14 at 16:01
Highace2
133113
I want to find a local maxima in a huge dataset with Excel and the way I've been trying is to compare the previous and the next value to make sure they are smaller eg.:
=IF(AND(C4>C3,C4>C5),"Local maxima","")
But the trouble with this formula is that if the peak stretches across multiple rows it won't catch that as local maxima. And that is despite the fact that it is a local maxima as values begins to fall.
(If you want to know why it is to find the local maxima in a set of spectrum data)
microsoft-excel
microsoft-excel
asked May 28 '14 at 16:01
Highace2
133113
asked May 28 '14 at 16:01
Highace2
133113
asked May 28 '14 at 16:01
Highace2
133113
asked May 28 '14 at 16:01
Highace2
133113
asked May 28 '14 at 16:01
Highace2
133113
133113
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
You're on the right track! You can do it with a couple of helper columns. See the graphic below.
The first helper column, "slope", uses Excel's SLOPE function. It calculates the slope between 2 adjacent points.
The 2nd helper column, "for labels", checks for a transition from a positive to negative slope. A transition from positive to negative slope is labeled "max".
Then you can label the graph with those maxima using a macro like this:
Sub CustomLabels()
Dim i, myCount, pt
ActiveSheet.ChartObjects("myChart").Activate
myCount = ActiveChart.SeriesCollection(1).Points.Count
For i = 1 To myCount
ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value
Next i
End Sub
answered May 28 '14 at 19:18
F106dart
1,613118
add a comment |
Another solution I would like add to this question is the following. It may become handy if the dataset is noisy or has a lot of x and y values. Create table with "Format as table" and do as following:
Column D (name in row1) as X
Column E (name in row1) as Y
New column F (name in row1) as slope and flash fill the column.
=slope(B2:B3,A2:A3)
New column G (name "solver" in row1), start in row 7
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")
Cell I1: Threshold value
It looks for the local maximum of Y (5 rows up and down), with a positive slope and an Y-value above a certain threshold. If successful, it returns the X-value.
An example, with the selected cell returning X (D214) 1.0004397
answered Dec 5 at 14:13
MKZ
1012
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f759813%2fexcel-find-local-maxima-with-multicell-peak%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You're on the right track! You can do it with a couple of helper columns. See the graphic below.
The first helper column, "slope", uses Excel's SLOPE function. It calculates the slope between 2 adjacent points.
The 2nd helper column, "for labels", checks for a transition from a positive to negative slope. A transition from positive to negative slope is labeled "max".
Then you can label the graph with those maxima using a macro like this:
Sub CustomLabels()
Dim i, myCount, pt
ActiveSheet.ChartObjects("myChart").Activate
myCount = ActiveChart.SeriesCollection(1).Points.Count
For i = 1 To myCount
ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value
Next i
End Sub
answered May 28 '14 at 19:18
F106dart
1,613118
add a comment |
You're on the right track! You can do it with a couple of helper columns. See the graphic below.
The first helper column, "slope", uses Excel's SLOPE function. It calculates the slope between 2 adjacent points.
The 2nd helper column, "for labels", checks for a transition from a positive to negative slope. A transition from positive to negative slope is labeled "max".
Then you can label the graph with those maxima using a macro like this:
Sub CustomLabels()
Dim i, myCount, pt
ActiveSheet.ChartObjects("myChart").Activate
myCount = ActiveChart.SeriesCollection(1).Points.Count
For i = 1 To myCount
ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value
Next i
End Sub
answered May 28 '14 at 19:18
F106dart
1,613118
add a comment |
You're on the right track! You can do it with a couple of helper columns. See the graphic below.
The first helper column, "slope", uses Excel's SLOPE function. It calculates the slope between 2 adjacent points.
The 2nd helper column, "for labels", checks for a transition from a positive to negative slope. A transition from positive to negative slope is labeled "max".
Then you can label the graph with those maxima using a macro like this:
Sub CustomLabels()
Dim i, myCount, pt
ActiveSheet.ChartObjects("myChart").Activate
myCount = ActiveChart.SeriesCollection(1).Points.Count
For i = 1 To myCount
ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value
Next i
End Sub
answered May 28 '14 at 19:18
F106dart
1,613118
You're on the right track! You can do it with a couple of helper columns. See the graphic below.
The first helper column, "slope", uses Excel's SLOPE function. It calculates the slope between 2 adjacent points.
The 2nd helper column, "for labels", checks for a transition from a positive to negative slope. A transition from positive to negative slope is labeled "max".
Then you can label the graph with those maxima using a macro like this:
Sub CustomLabels()
Dim i, myCount, pt
ActiveSheet.ChartObjects("myChart").Activate
myCount = ActiveChart.SeriesCollection(1).Points.Count
For i = 1 To myCount
ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value
Next i
End Sub
answered May 28 '14 at 19:18
F106dart
1,613118
edited May 28 '14 at 19:39
answered May 28 '14 at 19:18
F106dart
1,613118
answered May 28 '14 at 19:18
F106dart
1,613118
answered May 28 '14 at 19:18
F106dart
1,613118
1,613118
add a comment |
add a comment |
Another solution I would like add to this question is the following. It may become handy if the dataset is noisy or has a lot of x and y values. Create table with "Format as table" and do as following:
Column D (name in row1) as X
Column E (name in row1) as Y
New column F (name in row1) as slope and flash fill the column.
=slope(B2:B3,A2:A3)
New column G (name "solver" in row1), start in row 7
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")
Cell I1: Threshold value
It looks for the local maximum of Y (5 rows up and down), with a positive slope and an Y-value above a certain threshold. If successful, it returns the X-value.
An example, with the selected cell returning X (D214) 1.0004397
answered Dec 5 at 14:13
MKZ
1012
add a comment |
Another solution I would like add to this question is the following. It may become handy if the dataset is noisy or has a lot of x and y values. Create table with "Format as table" and do as following:
Column D (name in row1) as X
Column E (name in row1) as Y
New column F (name in row1) as slope and flash fill the column.
=slope(B2:B3,A2:A3)
New column G (name "solver" in row1), start in row 7
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")
Cell I1: Threshold value
It looks for the local maximum of Y (5 rows up and down), with a positive slope and an Y-value above a certain threshold. If successful, it returns the X-value.
An example, with the selected cell returning X (D214) 1.0004397
answered Dec 5 at 14:13
MKZ
1012
add a comment |
Another solution I would like add to this question is the following. It may become handy if the dataset is noisy or has a lot of x and y values. Create table with "Format as table" and do as following:
Column D (name in row1) as X
Column E (name in row1) as Y
New column F (name in row1) as slope and flash fill the column.
=slope(B2:B3,A2:A3)
New column G (name "solver" in row1), start in row 7
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")
Cell I1: Threshold value
It looks for the local maximum of Y (5 rows up and down), with a positive slope and an Y-value above a certain threshold. If successful, it returns the X-value.
An example, with the selected cell returning X (D214) 1.0004397
answered Dec 5 at 14:13
MKZ
1012
Another solution I would like add to this question is the following. It may become handy if the dataset is noisy or has a lot of x and y values. Create table with "Format as table" and do as following:
Column D (name in row1) as X
Column E (name in row1) as Y
New column F (name in row1) as slope and flash fill the column.
=slope(B2:B3,A2:A3)
New column G (name "solver" in row1), start in row 7
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")
Cell I1: Threshold value
It looks for the local maximum of Y (5 rows up and down), with a positive slope and an Y-value above a certain threshold. If successful, it returns the X-value.
An example, with the selected cell returning X (D214) 1.0004397
answered Dec 5 at 14:13
MKZ
1012
answered Dec 5 at 14:13
MKZ
1012
answered Dec 5 at 14:13
MKZ
1012
answered Dec 5 at 14:13
MKZ
1012
1012
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f759813%2fexcel-find-local-maxima-with-multicell-peak%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
