# 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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Replies
3
Views
798
Replies
1
Views
366
Replies
0
Views
445
Replies
3
Views
245
Replies
3
Views
281

1,216,405
Messages
6,130,393
Members
449,581
Latest member
econtent2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back