Identify max and mins in dataset automatically

I have a dataset, which when graphed is a much like a sine curve. I have attached a file which shows this (graphed in BLUE).

I need to identify the points which are where the Blue curve is increasing at the fastest rate and decreasing at the fastest rate. To do this i have graphed the derivative of the blue curve (graphed in RED). The fastest rate of increase and decrease in the blue line are marked by the maximum and minimum peaks in the Red lines.

What I need to do is identify the max and the mins in the red line and then add up the flow (Q) values between every max and minimum.

Right now I am doing this manually, and it is a very tedious task as I need to do this for over 1000 workbooks with Flow (Q) data. I am wondering if anyone can help me automate it? (either through the use of formulas or macros).

Note: as you can see in the red line, for every peak there appear to be 2-3 peaks immediately next to it. What I need to do is pick the biggest one essentially. Same goes for the mins.

I'd be grateful if anyone can help me automate any part of this process.

Cheers!

Can you try this.
Wasn't sure what you wanted with the result, so pasted it to Immediate (change to you liking :D)

Please Login or Register  to view this content.

Thanks so much for your reply rkey!
I wanted to essentially display the result (being the sum of the flow Q (between each max and min value of dQ/dt either next to the dQ/dt column or preferably in a separate table. But either is fine.

I tried to run the code, but nothing seemed to happen. It processes for a second or two but nothing happens, but there is no error either. I copied the code and made a macro in VBA for it. Did I do the right thing?

I've attached it again with the macro in the same workbook.

Yeah, you did right :D
Check the Immediate window for the results, after you ran it.
You can find them in your VBA Editor (alt+F11) and then selecting view -> Immediate window (ctrl+G)

Ah right, yup I just saw that. It's picking up the maximum and minimum points which is good.

But it's also picking up the other peaks present besides the major maximum and minimum points.
Is there anyway of avoiding those ones?

That is a real tricky one.
I wrote it in a way it is determining a maximum for as long as the dQ/dt is positive. As soon as it turns negative, it will add the max to the result. Then it will start determining the next Minimum etc.

As in your example this is the result at the start:
Row 2 Max:8,64
Row 14 Min:5,34
Row 30 Max:3,46
Row 34 Min:4,19
Row 40 Max:6,01

So row 34 which I think you don't want to see, is there because on row 34 it turns negative for 1 value, and after that it becomes positive again. The results is the display of the minimum on row 34.

So basicly you have to specify exactly when a maximum/minimum has to be counted and when not to be able to programmicly provide a solution.

Is there a way of adding in a threshold of somesort, where the maximum will only register if the value is over 30 and the minimum only if its less than -30, for example?

That should eliminate the problem of the inaccurate values showing up

Hey,

Yup that solves the problem. I've also modified it so that it shows the output in the sheet (the the row numbers and the max/min values in separate columns).

The last thing I had left for this was to find some automated way of adding up the values in Row G between each max and min (you can see these highlighted in red in the spreadsheet to see what i'm trying to do).

Can anyone help me with that?

The current Code is:

Please Login or Register  to view this content.

Here you go.
Changed your "," to a ";" (I have a comma as deciamal seperator), and made your display of data dynamic.

Please Login or Register  to view this content.

Thanks so much rkey! Everything actually works properly now and does what I wanted it to do!
You've been an immense help

Cheers!

No Problem, found it interesting to do :D
You can mark the tread as solved, so people can track it easier when looking for answers to similair questions.
Cheers
Rkey