Time - 2 things: Average mm:ss.00, Negitive Times

[Steve]

Hi,
1. I'm a Track coach and would like to keep track of over 100 runners'
times (ss.00 and mm:ss.00) throughout the season. I would like to be able to
calculate team averages as well as track individual's progress.
Meet 1 Meet 2 Net Difference (+ -)
runner 1
runner 2
runner 3
team averages:
I've read many of the posts, and none really solves my problems. Is there a
way to pre-format cells so I can just enter mm:ss.00 (or ss.00) without
having to preform complicated formulas and chant evocations to the cybergods?
Nothing in Custom seemed to work, and using 00:mm:ss.00 will not allow for
accurate averaging.
2. ALSO, I need to be able to show negitive times when runners do worse.
Excell doesn't like negitive times and only shows ######. Absolute Value will
produce the number, but is mis-leading, and again affects the average. Other
ideas?
Thank you!
Steve

 

[Peo Sjoblom]

You need to enter it as

hh:mm:ss.00


so if a time is 11 seconds 25 hundredths it should be entered as

00:00:11.25


then you can just use

=AVERAGE(range)

and use custom format ss.00 for the result

If you need to be able to get negative times you can do
tools>options>calculation and put a check mark next to 1904 date system. It
will add 1462 days to all dates you might have but that can be subtracted to
get the correct dates if necessary


--


Regards,


Peo Sjoblom

 

[David Biddulph]

You can probably save a little bit of typing and get away with typing in
0:11.25

 

[Steve]

I am greatful for your time, thank you. I was hoping to not have to deal
with hh: (or h: as you suggested), but it looks like your solution is the
only way. I found that [mm]:ss.00 keeps the minutes from being forced into
hours and allows for accurate averaging.
And thanks for the tip on the check by 1904 system to allow for negative
times - that works.
Thanks again,
Steve

 

[Peo Sjoblom]

There is no difference using the [mm]:ss.00, it's just that it will be
displayed the correct way,
the underlying value is still the same


--


Regards,


Peo Sjoblom

I am greatful for your time, thank you. I was hoping to not have to deal
with hh: (or h: as you suggested), but it looks like your solution is the
only way. I found that [mm]:ss.00 keeps the minutes from being forced
into
hours and allows for accurate averaging.
And thanks for the tip on the check by 1904 system to allow for negative
times - that works.
Thanks again,
Steve

You can probably save a little bit of typing and get away with typing in
0:11.25

 

[Steve]

Yes, thank you.

There is no difference using the [mm]:ss.00, it's just that it will be
displayed the correct way,
the underlying value is still the same


--


Regards,


Peo Sjoblom

I am greatful for your time, thank you. I was hoping to not have to deal
with hh: (or h: as you suggested), but it looks like your solution is the
only way. I found that [mm]:ss.00 keeps the minutes from being forced
into
hours and allows for accurate averaging.
And thanks for the tip on the check by 1904 system to allow for negative
times - that works.
Thanks again,
Steve

You can probably save a little bit of typing and get away with typing in
0:11.25
--
David Biddulph

You need to enter it as

hh:mm:ss.00


so if a time is 11 seconds 25 hundredths it should be entered as

00:00:11.25


then you can just use

=AVERAGE(range)

and use custom format ss.00 for the result

If you need to be able to get negative times you can do
tools>options>calculation and put a check mark next to 1904 date
system.
It will add 1462 days to all dates you might have but that can be
subtracted to get the correct dates if necessary


--


Regards,


Peo Sjoblom


Hi,
1. I'm a Track coach and would like to keep track of over 100
runners'
times (ss.00 and mm:ss.00) throughout the season. I would like to be
able
to
calculate team averages as well as track individual's progress.
Meet 1 Meet 2 Net Difference (+ -)
runner 1
runner 2
runner 3
team averages:
I've read many of the posts, and none really solves my problems. Is
there a
way to pre-format cells so I can just enter mm:ss.00 (or ss.00)
without
having to preform complicated formulas and chant evocations to the
cybergods?
Nothing in Custom seemed to work, and using 00:mm:ss.00 will not allow
for
accurate averaging.
2. ALSO, I need to be able to show negitive times when runners do
worse.
Excell doesn't like negitive times and only shows ######. Absolute
Value
will
produce the number, but is mis-leading, and again affects the average.
Other
ideas?
Thank you!
Steve