Using linest for flow rate calculation

[Robert_Steel]

I am hoping for some help to calculate the gradient of a dataset.
The data is the output from a datalogger that is connected to a weigher
The full dataset is approx 20,000 records (time and date vs weight of a
vessel)
The weight falls over time at a reasonably consistent rate as the contents
are used.
As the level gets low the vessel is quickly topped up.
Therefore is the full data is plotted the output is a rough sawtooth
profile.

I can use =LINEST(B1:B37,A1:A37) to give me the best fit gradient for the
first cycle
but as there are likely to be over 500cycles a manual method will be
tedious.
Plus I am likely to have 5 more datasets as part of this work.

The data is quite noisy so I have found spotting the start of a cycle
tricky.
The cycles are not a fixed length so this is another problem.

I would like to copy a formula down next to the data that will show a
value for the gradient once every cycle
leaving no value between.
But any way of more automatically picking out each cycle to apply the
formula would be a big help

I am using Excel2000 and am able to use VBA if necessary

Many thanks for taking the time to look at this

Cheers Rob

********************************************
Extract of data
06/07/2006 00:03:07 431.685
06/07/2006 00:04:07 433.305
06/07/2006 00:05:07 428.5425
06/07/2006 00:06:07 424.995
06/07/2006 00:07:07 420.24
06/07/2006 00:08:07 416.3625
06/07/2006 00:09:07 411.66
06/07/2006 00:10:07 407.205
06/07/2006 00:11:07 402.75
06/07/2006 00:12:07 398.1675
06/07/2006 00:13:07 394.05
06/07/2006 00:14:07 389.805
06/07/2006 00:15:07 385.53
06/07/2006 00:16:07 380.985
06/07/2006 00:17:07 376.1325
06/07/2006 00:18:07 371.52
06/07/2006 00:19:07 366.7875
06/07/2006 00:20:07 361.2
06/07/2006 00:21:07 357.2925
06/07/2006 00:22:07 352.6575
06/07/2006 00:23:07 347.5275
06/07/2006 00:24:07 342.915
06/07/2006 00:25:07 338.25
06/07/2006 00:26:07 333.4275
06/07/2006 00:27:07 329.025
06/07/2006 00:28:07 324.45
06/07/2006 00:29:07 319.47
06/07/2006 00:30:07 315.0225
06/07/2006 00:31:07 309.645
06/07/2006 00:32:07 305.28
06/07/2006 00:33:07 300.795
06/07/2006 00:34:07 296.1525
06/07/2006 00:35:07 291.09
06/07/2006 00:36:07 286.4775
06/07/2006 00:37:07 281.3175
06/07/2006 00:38:07 278.325
06/07/2006 00:39:07 272.25
06/07/2006 00:40:07 267.3675
06/07/2006 00:41:07 370.1475
06/07/2006 00:42:07 433.1175
06/07/2006 00:43:07 428.235
06/07/2006 00:44:07 423.6525
06/07/2006 00:45:07 419.505
06/07/2006 00:46:07 413.9775
06/07/2006 00:47:07 409.2225
06/07/2006 00:48:07 404.9475
06/07/2006 00:49:07 399.7275
06/07/2006 00:50:07 395.205
06/07/2006 00:51:07 390.63
06/07/2006 00:52:07 385.9575
06/07/2006 00:53:07 380.985
06/07/2006 00:54:07 376.1625
06/07/2006 00:55:07 372.0375
06/07/2006 00:56:07 366.7575
06/07/2006 00:57:07 361.6575
06/07/2006 00:58:07 357.33
06/07/2006 00:59:07 352.17
06/07/2006 01:00:07 348.105
06/07/2006 01:01:07 343.3425
06/07/2006 01:02:07 339.465
06/07/2006 01:03:07 334.83
06/07/2006 01:04:07 330.1275
06/07/2006 01:05:07 326.685

 

[Robert_Steel]

I am still hoping for any advice on this.
Even if it is to help me understand why it did not get a response so far.

Cheers Rob

 

[MrShorty]

Here's how I approached your problem (I only spent ~15 minutes on it --
I do have my own work to do).

First thought is, how to locate each cycle. Each cycle should be
indicated by a brief time where dw (w is column 3 in sample data) is
positive. The first thing I did was add a couple of columns for this.
Column 4=RC3-R[-1]C3, column 5=RC4>0. The TRUE's in column 5 can now be
used to bracket each cycle. Note that, because the datalogger appears
to log every minute, column 4 is also the average flow rate over that
minute in units/min.
Then I added a couple of columns to count the number of readings and
get a running total. Column6=IF(RC5,0,1+R[-1]C),
Column7=IF(RC5,0,RC4+R[-1]C). Note that you need to enter a 0 in the
first row of each of these columns. Then the average of these flow
rates can then be taken in column8=RC7/RC6.

For the first cycle of your sample data, this gave me an average flow
rate of -4.609 units/min. Using LINEST on the first cycle gave me
-4.637 units/min. The overall average flow rate calculated as (final
weight-initial weight)/(final time-initial time)=-4.609 units/min,
which as expected, is the same as I got.

I don't know if you have to use LINEST or not -- I'm sure the approach
I've used could be adapted to do just that. I also haven't done
anything to extract the average flow rate for each cycle into a
separate table, but that should be doable with an INDEX(MATCH(...))
combination.

Hope that helps.

 

[Robert_Steel]

Mr Shorty
That was a good reality check for me. I am perhaps over complicating it.
The use of the logical check to bracket the cycle is a good start. I may
just have to play with the threshold.
(Just because I have a +ve number does not mean the refill has fully
finished.)
Or I could simply discount the first and last reading of the cycle.

I concentrated on using LINEST to deliver a rate of change because i
belived it would provide the best fit to the data.
In practical terms the difference between LINEST and and average of the dw
values is within the bounds of acceptable error.

The starting and finishing point method must be treated with caution
though. It is significantly influenced by the value of the particular
points used. The more scatter the bigger the risk.

Thankyou for taking the time to look at this and respond.
Cheers Rob