Line Graph from Average figure on a given date

[Adhara]

Hi,

This is a complicated one which im stuggling to get my head around. Will post some data below to help.

I require a graph that logs the average detection delay over a period, previous week, month etc... but the solution also should work if i add new dates to the spreadsheet, and update the 'previous week' graph.
I have a formula to calculate the detection delay, but i cant work out how to average the detection delay on the report date and allow for new data, and update the graphs!
Not a problem if this needs to be clarified!

Good Luck. Help is appreciated as always

Example data

Report date Start Detect Detection Delay

23-Nov-10 22/11/2010 12:03 22/11/2010 12:04 00:01
23-Nov-10 23/11/2010 15:13 23/11/2010 15:22 00:09
23-Nov-10 23/11/2010 13:23 23/11/2010 13:25 00:02
23-Nov-10 23/11/2010 09:58 23/11/2010 10:02 00:04
23-Nov-10 23/11/2010 15:48 23/11/2010 16:48 01:00
24-Nov-10 24/11/2010 05:00 24/11/2010 05:54 00:54
24-Nov-10 24/11/2010 06:00 24/11/2010 06:01 00:01
24-Nov-10 23/11/2010 21:48 24/11/2010 08:33 10:45
24-Nov-10 24/11/2010 09:15 24/11/2010 09:15 00:00
24-Nov-10 24/11/2010 15:08 24/11/2010 15:08 00:00
25-Nov-10 25/11/2010 03:57 25/11/2010 04:30 00:33
25-Nov-10 24/11/2010 20:00 24/11/2010 20:02 00:02
25-Nov-10 25/11/2010 14:35 25/11/2010 14:36 00:01
25-Nov-10 24/11/2010 18:29 25/11/2010 09:58 15:29
25-Nov-10 25/11/2010 13:20 25/11/2010 13:25 00:05
26-Nov-10 26/11/2010 03:37 26/11/2010 04:19 00:42
26-Nov-10 26/11/2010 10:14 26/11/2010 10:16 00:02
26-Nov-10 26/11/2010 08:59 26/11/2010 09:05 00:06
26-Nov-10 26/11/2010 16:13 26/11/2010 16:16 00:03
27-Nov-10 27/11/2010 06:35 27/11/2010 06:37 00:02
28-Nov-10 27/11/2010 23:20 27/11/2010 23:20 00:00
28-Nov-10 28/11/2010 07:50 28/11/2010 07:54 00:04
28-Nov-10 28/11/2010 08:01 28/11/2010 08:13 00:12
28-Nov-10 28/11/2010 09:50 28/11/2010 09:53 00:03

[JulieM]

You have included phone numbers in your example data which will make graphing it rather difficult! (i think)

[squiggler47]

assuming data in colums a-D

you can just =average(D2:D65536)
blanks are ignored!

i would recommend using as few rows in the formula as is practical, if 2000 is better given the data example 5 per day is about max so 365*5 = 1800 data lines for a full year! (=average(D2:D2000))

[Adhara]

Thanks for the replies.

There are no phone numbers in the data, so i should be ok

I've managed to come up with a solution to average the occurences on each date rather than the whole sheet:

=SUMIF('Incident tracker'!A:A,'Average Delay'!A4,'Incident tracker'!E:E)/COUNTIF('Incident tracker'!A:A,A4)

Where A = Date E= Detection Delay.

Will mark this as solved. Thanks