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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,657
Office Version
  1. 365
Platform
  1. Windows
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,657
Office Version
  1. 365
Platform
  1. Windows
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
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,657
Office Version
  1. 365
Platform
  1. Windows
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
 

ecarney14

New Member
Joined
Sep 21, 2006
Messages
42
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,657
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,141,705
Messages
5,707,976
Members
421,539
Latest member
zuniBM

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
Top