Filtered Arrays in formulae

A

Alex Delamain

I have a table of data in the form shown below
Date Time Product Measure Lab
26/08/2004 07:22:30 AAA 55.1 53.2
26/08/2004 08:16:00 AAA 56.2
26/08/2004 09:10:00 AAA 54.3
26/08/2004 10:09:30 AAA 55.1
26/08/2004 10:50:00 AAA 53.5 52.5
26/08/2004 11:44:00 AAA 53.6
26/08/2004 12:38:30 BBB 49.8
26/08/2004 13:32:30 BBB 51.1 51.1
26/08/2004 14:26:30 BBB 50.2
26/08/2004 15:04:00 BBB 48.9
26/08/2004 15:22:00 BBB 49.7 49.6
26/08/2004 16:18:00 BBB 50.3
26/08/2004 17:12:30 AAA 55.2
26/08/2004 18:06:30 AAA 54.7 54.5

I want to produce a table of correlation coefficients, slopes an
offsets product by product (and ultimately to include a date range) bu
am really struggling to find a way.

I have named ranges for each column running from row 2 to the las
entry so they expand with the data but when I start nesting If(
statements inside Slope() and Intercept() I keep getting #div/0!

It would be simple to calculate each one separately (or use an xy char
showing the equation) but I have a huge range of products and variable
I want to track over time so it would be very timeconsuming

Help please!!!
 
Top