Help Calculating Total Average - Not Running Average

[tara_922]

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=512 border=0><COLGROUP><COL style="WIDTH: 162pt; mso-width-source: userset; mso-width-alt: 7899" width=216><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 162pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #e6b9b8" width=216 height=20>Average Dif between Wait & ETA</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 63pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #e6b9b8" width=84>Average ETA</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #e6b9b8" width=127>Average Wait Time</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 32pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=42>Calc 1</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 32pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=43>Calc 2</TD></TR><TR style="HEIGHT: 27.95pt; mso-height-source: userset" height=37><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; HEIGHT: 27.95pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=37>-1:00</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc">2:00</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f2dddc">2:30</TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-0.04</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-0.04</TD></TR><TR style="HEIGHT: 27.95pt; mso-height-source: userset" height=37><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: red; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; HEIGHT: 27.95pt; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=37>-0:30</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">0.00</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-0.02</TD></TR></TBODY></TABLE>

Cells above are
N5, O5, P5, Q5, R5
N6, O6, P6, Q6, R6

In N5 my formula = IF($R5="","",REPT("-",$R5<0)&TEXT(ABS($R5),"[h]:mm"))

In O5 my formula = IF(ISERROR(AVERAGE(G:G)),"",AVERAGE(G:G))

In P5 my formula = IF(ISERROR(AVERAGE($L$5:$L$101)),"",AVERAGE($L$5:$L$101))

In Q5 my formula = G5-L5 (G5 is estimated wait L5 is actual wait)

In R5 my formula = IF(AVERAGE(Q$5:Q5=""),"",AVERAGE(Q$5:Q5))

So my question is, how do I make column in the total average difference instead of a running difference. I've tried a few things to no avail. Please help!

Thanks a ton!

Tara

 

[MikeWx]

It's unclear to me which value you are trying to change, and what you expect the result to be. Can you provide a little more information please?

 

[tara_922]

Hiya Mike!

That was pretty unclear, I'm sorry. Thanks for pointing that out to me. I need column N which is the Average Dif between Wait & ETA to be a total average and not a running average.

Thanks!

Tara

 

[p45cal]

The only thing making this currently a running average is in the Calc2 column, the
Q$5:Q5
in
= IF(AVERAGE(Q$5:Q5=""),"",AVERAGE(Q$5:Q5))

Change both instances to the likes of:
Q$5:Q$101
(101 because that's what you've used in column P)
or:
Q:Q
(because you used the whole column in the formula in column O)

 

[tara_922]

Hiya!

I tried that, but my formula in column N is

=IF($Q5="","",REPT("-",$Q5<0)&TEXT(ABS($Q5),"[h]:mm"))

So changing it to R:R or R$5:R$101 in all instances didn't work. Any ideas why not?

Thanks,
Tara

 

[p45cal]

You said your formula in column N was:
IF($R5="","",REPT("-",$R5<0)&TEXT(ABS($R5),"[h]:mm"))
Leave it alone.

Instead, as I said, change the formula in the Calc2, that's column R. to either
IF(AVERAGE(Q$5:Q$101=""),"",AVERAGE(Q$5:Q$101))
or
IF(AVERAGE(Q:Q=""),"",AVERAGE(Q:Q))

ps. there's not much point in having a column of figures all exactly the same, so this formula only need be in one cell, just as all the values in column R will be the same, so only one cell is necessary.

 

[tara_922]

Ah! Gotcha! Sorry about that.

Thanks!

Tara