How to display -/+ variences with time? 2

[CP4840]

SOS,
I am looking for a formula that will take the average of the daily variance. When I perform an average function I get a #div/0! error. Can anyone help?

Tote Completion Times.xls
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
12	Monday			Tues			Wed			Thurs
13	11/14/05			11/15/05			11/16/05			11/17/05			Week Ending 11/17/05
14	Scheduled Completion Time	Actual Completion Time	Variance	Scheduled Completion Time	Actual Completion Time	Variance	Scheduled Completion Time	Actual Completion Time	Variance	Scheduled Completion Time	Actual Completion Time	Variance	Average	Variance
15	23:15	0:01	00:46	23:15	0:01	00:46							0:01	#DIV/0!
16	23:15	23:29	00:14	23:15	23:29	00:14							23:29	#DIV/0!
17	23:15	23:59	00:44	23:15	23:59	00:44							23:59	#DIV/0!
18	23:15	0:01	00:46	23:15	0:01	00:46							0:01	#DIV/0!
19	23:15	23:00	-00:15	23:15	23:00	-00:15							23:00	#DIV/0!
20	23:15	21:49	-01:26	23:15	21:49	-01:26							21:49	#DIV/0!
21	23:15	22:52	-00:23	23:15	22:52	-00:23							22:52	#DIV/0!
22	23:15	22:11	-01:04	23:15	22:11	-01:04							22:11	#DIV/0!
23	23:15	23:05	-00:10	23:15	23:05	-00:10							23:05	#DIV/0!
Nov 05-

 

[barry houdini]

You have a problem averaging because the variances are in text format.

you either need to have the variances in a numeric format (which presents problems because you can't normally show negative times) or you need to do some sort of conversion from text to numeric.

Before I make any suggestion can I establish a couple of things?

Would you consider showing the variances as whole minutes, e,g 75 instead of 01:15? This might make the calculations easier.

If one variance is +2 hours and the next -2 hours your average will be zero, the same as if both were completed on time, is this what you want?

Will you need to do any further calculations with the averages computed (if so it helps to keep them as numeric)

 

[CP4840]

I don't think it would make a difference if the variance was in minutes.
Maybe it will help if I explain what I am trying to measure. I run a distribution center and my supervisors need a tool that they can use to determine if they are making the scheduled completion times for each group of work. Each group of work is batched in group names represented by 3 charters. For example, the AE1 group needs to be picked, packed and shipped by 23:15 or 11:15pm. There are 8 departments that will be completing this work and for this example we will use XU (which is one of the pick zones). At the end of the day we run a query that tells us what time each shipping unit’s ID number was completed, by group (AE1) and by zone (XU). They will then plug those times into this spreadsheet. This way they can see the average time of the night the route was completed, daily and weekly. They can also see how early (which would be recognized by a neg time) or late (which would show a positive time) they were to the scheduled departure time. This way each supervisor can figure out at what time of the night they are struggling to meet the scheduled departure times and adjust their resources accordingly. They can also determine if the volume of that group is just too large to make that time and do we need to create a new group to reduce the volume.

Anyway, that may be a little too much info but I thought it would help you understand where I am going with this. Maybe I am making this too complicated and if you have an easier way I am all for it.

At this point I don't think I will be doing anything else with the averages computed.

 

[barry houdini]

It's not a particularly pretty formula but this in E15 copied down and elsewhere

=MIN(ABS(C15-D15),1-ABS(C15-D15))*1440*IF(ROUND(MOD(MIN(ABS(C15-D15),1-ABS(C15-D15))+C15,1),8)<>ROUND(MOD(D15,1),8),-1,1)

format as general

should give you the variance in minutes.

You could then use a regular average formula in column P

 

[CP4840]

Again, you are the man!!! Thanks for the help. I tried to use an average formula but because there are 0 values in some of the cells I had to go with the one below. There was probally a easier way to do it but it worked. Again, thanks!!!

=SUM(E4,H4,K4,N4,Q4)/SUM(COUNTIF(E4,"<>0"),COUNTIF(H4,"<>0"),COUNTIF(K4,"<>0"),COUNTIF(N4,"<>0"),COUNTIF(Q4,"<>0"))