Measuring difference between many local maxima/minima

**Tim E** · 05-09-2013, 01:23 AM

Hi all,

I have a series of data that measure the mass of a container that is filled irregularly and emptied almost continuously. In order to measure the total mass added to the system, I would like to measure the difference between the several local minima and the maxima that follow (thus measuring the mass added each time).

Attached is sample data. Sample Data.xlsx

Hope you can help!

Tim

**FDibbins** · 05-09-2013, 01:39 AM

Hi and welcome to the forum

aangename kennis

to find the min, use =min() and for the max, use (gee who would have guessed huh? lol) =max()

my question is, are these mins/maxs based on a certain period/time frame/qty of measurements/what?

**Tim E** · 05-09-2013, 01:47 AM

Ah, flaunting one of our eleven official languages I see

Thanks for the welcome!

I know the min/max functionality of excel...the problem comes in that the minima and maxima are often at different heights and different times, making it challenging to measure the actual mass added to the containers. The sample sheet I attached to the post above is actual data I am using, and shows very clearly what I am using.

Currently, I have found a solution that works about 95% of the time but is not very robust. It relies on looking at the gradient before and after a point and determining if it is a turning point, then looks at the array of data for the next 2 hours ahead (the offloading doesn't take more than 1-2 hours) and finds the max there, and subtracts the min and max. The challenge is using a robust formula for the min value, and for not double counting.

The formula I use is =IF(SLOPE(B6:B8,{1,2,3})>0.1,IF(B6-B5<=0,IF(ISNUMBER(D5),"",MAX(B6:B18)-B6),""),"") where column B contains the container mass data and column D contains the above formula.

**FDibbins** · 05-09-2013, 02:16 AM

Well when I grew up there, there were only 2 official languages lol (ex-pat here from PE and Klerksdorp)

OK you lost me lol. Would it help if you could confine the max to a specific range?
somehow using this format...
=MAX(IF(A2:A7=F2,B2:B7,0)) entered as an array formula

**Tim E** · 05-09-2013, 02:53 AM

lol explains it!

Not sure if I understand the formula you posted...what is the logic A2:A7=F2 testing?

So if you look at the data, it drops gradually for a certain period of time then spikes up (indicating filling) then drops gradually again (not necessarily for the same period of time) and then spikes and so on. The gradual drop is material being used. The spike is a delivery of material.

An example of this would be, say, a water tank that is constantly being drained at a slow rate (the gradual slope), but every now and then will receive a (very) large bucket full of water (the spike). I am interested in measuring how much water is added.

So my idea was:
1) Pick out spikes
2) Measure the difference between max and min
3) Sum all of these calculations over the whole month

The challenges with some of these:

1) Picking out a spike that is actually a spike and not just a minor variation
2) Counting an addition once