Difference in times not calculating correctly

[tara_922]

Column O - Average Dif between Wait & ETA = 0:00
Column P - Average ETA = 0:53
Column Q - Average Wait Time = 0:46

There are six times that make up the dif between wait & ETA they are:
-0:05
0:06
0:04
0:00
0:00
0:00

The average of these should round up to 0:01 min and not the 0:00 that shows up.

I have two rows that calculate date. One is Column R that calculates the difference between the actual wait time and the estimated wait time.

Column S calculates once the average of column R. The formula is =IF(AVERAGE(R$8:R$108=""),"",AVERAGE(R$8:R$108))

Column O has the formula
=IF(AVERAGE($S8=""),"",REPT("-",AVERAGE($S8<0))&TEXT(ABS($S8),"[h]:mm"))
This allows for negative time to be input.

However, that formula isn't working. Any suggestions?

I'm sorry I can't use excel Jeanie because I'm at work and limited on what I can download and do.

Thanks in advance!

Tara

 

[perco754]

Hi,

My suggestion is once you've got the data that you convert it to integers for further manipulations/calculations. I don't think that Excel can handle negative Times in functions like AVERAGE. The Time format is always expressed w.r.t a base/start date (in my Excel version it is 1900-01-05 or May 5'th 1900). You see why there can't be a negative value?

If I take the values you provided and manually calculate the average, by summarize all values >= 0 minus the value < 0, I get the result value 0:005 and this will be shown as 0:01 if you have formated the cells as I espect m:ss.

If, you in spite of my suggestion in using integers for calculation decide to stick to the Time format, you'll need to handle negative values manually.

BR,
perco

 

[MikeWx]

Tara,

Perco is correct, negative time values are messy in Excel. The following formula in Cell A8 will do what you want, but it is rather cumbersome. As my example data shows, it will even give a negative average if that is what the data indicates. Maybe you can use it in your solution.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>-0:05</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">0:06</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">0:04</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">0:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>-0:12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">0:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>-0:01</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Mike

 

[tara_922]

Hiya Mike!

Thanks for the reply! Could you post the formula please?

Thanks again!

Tara

 

[MikeWx]

Sorry Tara, I thought I had the formula with the data when I posted it. Here is the formula in Cell A8.

=IF(SUM(IF(ISNUMBER(A1:A6),A1:A6,RIGHT(A1:A6,LEN(A1:A6)-1)*-1))>=0,AVERAGE(IF(ISNUMBER(A1:A6),A1:A6/60,RIGHT(A1:A6,LEN(A1:A6)-1)/-60)),"-"&TEXT(AVERAGE(IF(ISNUMBER(A1:A6),A1:A6/-60,RIGHT(A1:A6,LEN(A1:A6)-1)/60)),"m:ss"))

This is an array formula, so press Cntrl-Shift-Enter instead of just Enter.

Mike

 

[tara_922]

Thanks Mike!

Tara