Multiple Maximums

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
I have a large set of data, and it has several peaks over time. Essentially I would like to find the different max points, and their time. After distinguishing the maximum points, I would like to take a period of time between the first and second maximum point and find the minimum point.

With the minimum point, I want to use that to subtract from each data point.

Here's a simplified version of what i am asking.

1 -1
2 1
3 0
4 -5
5 -4
6 -3
7 -2
8 7
9 5
10 0
11 -3
12 0


The max pts would be 1 and 7, the lowest point between this period is -5.
Then the third colum would take each value in column 2 and subtract -5 from it.

Sorry it sounds a little confusing, but i feel finding multiple maxiums must be possible on excel. Thanks for any help!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
ecarney14

Would it be possible that, say, the third value in column 2 could also be 1 meaning that although 1 is still the local maximum it occurs in two (or more) adjacent cells?
 
Upvote 0
i don't think i am really understanding what you are asking...
What I am asking is: Could the data look like this where there are adjacent equal maximums in rows 2 and 3 and adjacent equal minimums in rows 5, 6 and 7?
Mr Excel.xls
ABCD
11-1
221max
331max
44-2
55-5min
66-5min
77-5min
887
998max
10100
1111-3min
12120
Sheet1
 
Upvote 0
Yes, although the numbers I am using are probably 19 decimals long, so the probability of them being the same number is very slim.

Sorry, I was using a simple version to try and explain what i wanted to do. But I understand what you are saying now
 
Upvote 0
Yes, although the numbers I am using are probably 19 decimals long, so the probability of them being the same number is very slim.
Ok, on the assumption that the numbers are unlikely to be equal, this at least will identify local maximums/minimums. Is that some use to you?

Formula in D2 (copied down):
Code:
=IF((B2>B1)*(B2>B3),"max",IF((B2<B1)*(B2<B3),"min",""))
Mr Excel.xls
ABCDE
11-1
221max
330 
44-5min
55-4 
66-3 
77-2 
887max
995 
10100 
1111-3min
12120 
13
MaxMin
 
Upvote 0
Thanks for your help. I did write a condition similar to what you have, but placing the value of the cell rather than 'max or min. Do you have an idea on the second part of things?

For instance, a way in a macro, take the min value between the first two maximums and use it in another formula.

Basically I want the macro, to notice 5 is the min between the two max. And then put it in a formula such as D1=A1-min
D2= A2-min
 
Upvote 0
ecarney14

Just to clarify the requirement, for the sample data given:
You mentioned that 5 was the min between the first two maximums but it is actually -5. What would be subtracted?
Would the min be subtracted from every row?
or just rows 2 to 8 (from 1st max to 2nd max)?
or just rows 3 to 7 (between 1st max and second max)?

Perhaps you could advise for this small sample ALL the resultant cell values you would expect as results?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top