# Analyzing dynamic data

#### benihanachef

##### New Member
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.

benihanachef