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.

Max		12	12
Date	Period (days)	Tank 1 (L)	Tank 2 (L)
1/3/2014	0	10.02	11.48
1/4/2014	1	10.17	11.56
1/5/2014	2	10.19	0.24
1/6/2014	3	10.33	0.30
1/7/2014	4	10.54	0.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