Time Date and Graph help (complex formula)

**mrbadss** · 02-20-2009, 02:42 AM

Guys, First, thanks for your help on this one.. Here is what I am trying to do. (kudos if you can figure out how to do it)

1. I am getting a series of times throughout the night (ie 11:40, 12:10, 1:20, 1:40, etc)... what I want to do is to calculate the difference between them (in minutes) and then average those numbers..
Issues with doing this the easy way:
-I need to do this in one cell
-The time can be before and after midnight
-There is not a fixed number of "times" throughout the night so its an open row (ie there could be 10 times or 20 times)
-Each morning, I will paste the times in there and I want to get the average difference between them.

Any ideas?

2. How can I then graph these times on a single line? example:
If I get 1:20, 1:40, 2:20, 3:00 I would want a graph that looks like this:

-|--|-----|---------|-----------------

Basically the distance between the lines is consistent with the amount of minutes/ hours between them...

thoughts?

THANKS for the help guys..

Reed

**DonkeyOte** · 02-20-2009, 04:35 AM

I'm afraid I can't help much on the Chart stuff... there are a few chart whizzes around though (1 in particular)

Re: the average... you don't specify where the data is entered...

If we assume then that you enter your time values in row 1 and as you say it's variable... so some days maybe just A1:F1 contain values whereas other times A1:T1... let's then assume we use A1:Z1 as our range so as to be sure we encapsulate all possible entries... we can calculate the Average between times using:

Please Login or Register  to view this content.

Time in XL is decimal ... multiplying the result by 1440 (24 hours * 60 mins) will take the resulting average decimal and convert to integer... eg 30 mins in Decimal terms 0.020833 (to 6 decimals) ... multiplying by 1440 will convert that decimal to integer - ie to 30.

Does that help ?

**Andy Pope** · 02-20-2009, 05:07 AM

Is this what you mean in terms of the chart?
It's a xy-scatter with the Y value being constant.

If not can you post example workbook

**mrbadss** · 02-20-2009, 01:15 PM

Andy,
Thanks... that was the chart piece I was looking for...

On the formula, Attached is a spreadsheet...

Legend
1. Green is the raw data
2. Red is the data I dont want to have the calc manually
3. Pink is the weird one because it goes over midnight and needs a special formula (and I cant figure out how to put the red and pink formula together)
4. Yellow is the result I want in one cell (essentially an average of the red/pink times)

Thanks for your help guys...

Reed

**DonkeyOte** · 02-20-2009, 01:23 PM

The formula provided already will work - though you would need to adjust the ranges to reflect your real setup, enter the first time correctly - and remove the 1440* to get the result in time format (rather than integer) as outlined previously.

The first time entry should be 0:30:00 ... 12:30:00 is midday + 30 mins... not midnight.

Assuming you fix that your formula for the yellow cell formula becomes:

Please Login or Register  to view this content.

The above will handle times crossing midnight etc...
(not that you have any such instances in your sample)

**mrbadss** · 02-20-2009, 02:07 PM

Donkey... Thank you... I figured that piece out based on what you were saying... THANK YOU!

ON the graph piece though, I cant get the graph to come out right... attached is the format of the raw data and I want to chart it where the Y axis is the date, x axis is a time window (from say midnight to 8 am), and the dots on the line represent each of the times between (essentially showing the intervals graphically).

Thanks guys!

Reed

**DonkeyOte** · 02-20-2009, 02:11 PM

OK I'm glad the Average is resolved... I will leave the Chart stuff to Andy Pope ... in the meantime be sure to check out his site: http://www.andypope.info/charts.htm

**Andy Pope** · 02-21-2009, 07:54 AM

The difficult part is generating the Y data points for each series.

In the attached I used a range of dates for the first series and an named range for the second.
Either way you will require a set of data for each series in the chart.