# How to display -/+ variences with time? 2

#### CP4840

##### New Member
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
CDEFGHIJKLMNOP
12MondayTuesWedThurs
1311/14/0511/15/0511/16/0511/17/05Week Ending 11/17/05
14Scheduled Completion TimeActual Completion TimeVarianceScheduled Completion TimeActual Completion TimeVarianceScheduled Completion TimeActual Completion TimeVarianceScheduled Completion TimeActual Completion TimeVarianceAverageVariance
1523:150:0100:4623:150:0100:460:01#DIV/0!
1623:1523:2900:1423:1523:2900:1423:29#DIV/0!
1723:1523:5900:4423:1523:5900:4423:59#DIV/0!
1823:150:0100:4623:150:0100:460:01#DIV/0!
1923:1523:00-00:1523:1523:00-00:1523:00#DIV/0!
2023:1521:49-01:2623:1521:49-01:2621:49#DIV/0!
2123:1522:52-00:2323:1522:52-00:2322:52#DIV/0!
2223:1522:11-01:0423:1522:11-01:0422:11#DIV/0!
2323:1523:05-00:1023:1523:05-00:1023:05#DIV/0!
Nov 05-

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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)

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.

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

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"))

Replies
1
Views
371
Replies
1
Views
217
Replies
2
Views
278
Replies
5
Views
634
Replies
5
Views
1K

1,216,496
Messages
6,130,985
Members
449,612
Latest member
geniusufo007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back