Average Time Difference

[jonhunt]

I have a deadline time entered as 10:11 in B7. In c7 through s7 I have the actual times, i.e. 10:06. In most instances the deadline was met, but in a few it was not. I'd like to have the average time variance between the deadline and the actual. I'm not very sophisticated in formulas, so my way would probably involve a string that's far too long. Is there a simple way to do this?

Thanks,

Jon

[pdberger]

Jonhunt --

Simple approach, not very elegant:

Cell C8: =$b$7-c7

Then copy from C8 out to S8.

Cell T8: =VARP(C8:S8)

HTH

"jonhunt" wrote:

>
> I have a deadline time entered as 10:11 in B7. In c7 through s7 I have
> the actual times, i.e. 10:06. In most instances the deadline was met,
> but in a few it was not. I'd like to have the average time variance
> between the deadline and the actual. I'm not very sophisticated in
> formulas, so my way would probably involve a string that's far too
> long. Is there a simple way to do this?
>
> Thanks,
>
> Jon
>
>
> --
> jonhunt
> ------------------------------------------------------------------------
> jonhunt's Profile: http://www.excelforum.com/member.php...o&userid=35370
> View this thread: http://www.excelforum.com/showthread...hreadid=560826
>
>

[Bob Phillips]

Jon,

Not clear whether over and under are treated the same or not, but try this

=AVERAGE(IF(C7:S7<>"",ABS(B7-C7:S7)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jonhunt" <[email protected]> wrote in
message news:[email protected]...
>
> I have a deadline time entered as 10:11 in B7. In c7 through s7 I have
> the actual times, i.e. 10:06. In most instances the deadline was met,
> but in a few it was not. I'd like to have the average time variance
> between the deadline and the actual. I'm not very sophisticated in
> formulas, so my way would probably involve a string that's far too
> long. Is there a simple way to do this?
>
> Thanks,
>
> Jon
>
>
> --
> jonhunt
> ------------------------------------------------------------------------
> jonhunt's Profile:
http://www.excelforum.com/member.php...o&userid=35370
> View this thread: http://www.excelforum.com/showthread...hreadid=560826
>

[jonhunt]

I couldn't get either of these to work, but I did take the $b$7-c7 and copy it out and then just averaged the results.

[Bob Phillips]

I'll bet you didn't array enter it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jonhunt" <[email protected]> wrote in
message news:[email protected]...
>
> I couldn't get either of these to work, but I did take the $b$7-c7 and
> copy it out and then just averaged the results.
>
>
> --
> jonhunt
> ------------------------------------------------------------------------
> jonhunt's Profile:
http://www.excelforum.com/member.php...o&userid=35370
> View this thread: http://www.excelforum.com/showthread...hreadid=560826
>