Analyzing dynamic data

[benihanachef]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Max[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Period (days)[/TD]
[TD]Tank 1 (L)[/TD]
[TD]Tank 2 (L)[/TD]
[/TR]
[TR]
[TD]1/3/2014[/TD]
[TD]0[/TD]
[TD]10.02[/TD]
[TD]11.48[/TD]
[/TR]
[TR]
[TD]1/4/2014[/TD]
[TD]1[/TD]
[TD]10.17[/TD]
[TD]11.56[/TD]
[/TR]
[TR]
[TD]1/5/2014[/TD]
[TD]2[/TD]
[TD]10.19[/TD]
[TD]0.24[/TD]
[/TR]
[TR]
[TD]1/6/2014[/TD]
[TD]3[/TD]
[TD]10.33[/TD]
[TD]0.30[/TD]
[/TR]
[TR]
[TD]1/7/2014[/TD]
[TD]4[/TD]
[TD]10.54[/TD]
[TD]0.37[/TD]
[/TR]
</tbody>[/TABLE]












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