Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using linest for flow rate calculation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using linest for flow rate calculation
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using linest for flow rate calculation
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=RC40. 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. -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=560415 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using linest for flow rate calculation
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
growth rate but not average | Excel Discussion (Misc queries) | |||
Solving for discount rate in present value calculation | Excel Worksheet Functions | |||
XNPV vs. NPV(quarterly) different results | Excel Worksheet Functions | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
linest r-squared bug when calculating forced through zero? | Excel Worksheet Functions |