Analyzing dynamic data

benihanachef

New Member
Joined
Aug 27, 2008
Messages
8
Hi all,

I would like to automate the analysis of data that I pull from a database via automated SQL query. Some of the problems I've run into are: data array is not always the same size. It always contains the same number of columns but the rows will vary some. So, first off, I need a way to determine the address of the last row (for use in my analysis). The next problem is that I need to analyze the slope of my data (for trend analysis) but sometimes the data pulled will contain a reset somewhere in the middle.

The data pulled will monitor the liters of fluid collected in a tank, to a max of 12L. I want to predict when my next tank drain will need to be performed. I think I can use COUNTA or maybe ISBLANK to determine the last row of data but I'm having trouble figuring out how to deal with the break when the data contains a tank reset as with Tank 2, below.

Max1212
DatePeriod (days)Tank 1 (L)Tank 2 (L)
1/3/2014010.0211.48
1/4/2014110.1711.56
1/5/2014210.190.24
1/6/2014310.330.30
1/7/2014410.540.37

<tbody>
</tbody>












An example of the formula I am currently (manually) using to predict the next tank empty is:
=(D1-D7)/MAX(SLOPE(D3:D4,B3:B4),SLOPE(D5:D7,B5:B7))

I then take the outcome (days) and add that to the date of the latest row of data (A7) using:
=LOOKUP(10^10,$A:$A)+F3 (F3 would contain the above formula).

My vision of the final, automated formula, would be something like this plain language programming:
=IF(tank reset found,(D1-address of latest data)/MAX(SLOPE(pre reset array),SLOPE(post reset array)),(D1-address of latest data)/SLOPE(entire dataset array)

I prefer to do this using formulas, if possible. I'm not averse to using CSE, if necessary, but I haven't used array formulas much so would need good level of detail. Same goes for macros.

Thanks in advance!

benihanachef
 

Forum statistics

Threads
1,085,989
Messages
5,387,127
Members
402,045
Latest member
Hidalgo

Some videos you may like

This Week's Hot Topics

Top