Averaging seconds

[Ospreyguy]

I am trying to figure out how to average a set of seconds to be used in a
graphing. Here is the data I have.

Date ID Time
May-05 4223 :40
Jun-05 4883 :26
Jul-05 1363 :24
Aug-05 286 :11
Sep-05 199 :10
Oct-05 2836 :09
Nov-05 1787 :09
Dec-05 2055 :10
Jan-06 2905 :12
Feb-06 2760 :32
Mar-06 2302 :31
Apr-06 1636 :19
May-06 1985 :10

Date ID Time
May-05 11058 :21
Jun-05 12352 :25
Jul-05 9425 :17
Aug-05 10314 :13
Sep-05 6168 :08
Oct-05 4958 :09
Nov-05 4142 :09
Dec-05 3206 :10
Jan-06 4378 :12
Feb-06 4320 :12
Mar-06 5232 :15
Apr-06 4038 :18
May-06 4278 :14

I need to average the seconds for each ID #. I have used the custom format
to make sure it is all in the [h]mm:ss format and I keep getting the #DIV/0!
error. Any suggestions?

 

[Bob Phillips]

Try

=AVERAGE(IF(B2:B200=4223,C2:C200))

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Ospreyguy]

I am still getting the #DIV/0! error... if I use =AVERAGEA(C2,G2) it gives me
0:00:00. All I want to know is if it took 21, 42, 35, and 51 seconds to do a
specific task what is the average? But also keeing it in the colon format...

Try

=AVERAGE(IF(B2:B200=4223,C2:C200))

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I am trying to figure out how to average a set of seconds to be used in a
graphing. Here is the data I have.

Date ID Time
May-05 4223 :40
Jun-05 4883 :26
Jul-05 1363 :24
Aug-05 286 :11
Sep-05 199 :10
Oct-05 2836 :09
Nov-05 1787 :09
Dec-05 2055 :10
Jan-06 2905 :12
Feb-06 2760 :32
Mar-06 2302 :31
Apr-06 1636 :19
May-06 1985 :10

Date ID Time
May-05 11058 :21
Jun-05 12352 :25
Jul-05 9425 :17
Aug-05 10314 :13
Sep-05 6168 :08
Oct-05 4958 :09
Nov-05 4142 :09
Dec-05 3206 :10
Jan-06 4378 :12
Feb-06 4320 :12
Mar-06 5232 :15
Apr-06 4038 :18
May-06 4278 :14

I need to average the seconds for each ID #. I have used the custom format
to make sure it is all in the [h]mm:ss format and I keep getting the #DIV/0!
error. Any suggestions?

 

[Bob Phillips]

You asked how to get an average per id. I showed how for one particular id.
Format has nothing to do with it just use a time format.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I am still getting the #DIV/0! error... if I use =AVERAGEA(C2,G2) it gives me
0:00:00. All I want to know is if it took 21, 42, 35, and 51 seconds to do a
specific task what is the average? But also keeing it in the colon format...

Try

=AVERAGE(IF(B2:B200=4223,C2:C200))

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

I am trying to figure out how to average a set of seconds to be used in a
graphing. Here is the data I have.

Date ID Time
May-05 4223 :40
Jun-05 4883 :26
Jul-05 1363 :24
Aug-05 286 :11
Sep-05 199 :10
Oct-05 2836 :09
Nov-05 1787 :09
Dec-05 2055 :10
Jan-06 2905 :12
Feb-06 2760 :32
Mar-06 2302 :31
Apr-06 1636 :19
May-06 1985 :10

Date ID Time
May-05 11058 :21
Jun-05 12352 :25
Jul-05 9425 :17
Aug-05 10314 :13
Sep-05 6168 :08
Oct-05 4958 :09
Nov-05 4142 :09
Dec-05 3206 :10
Jan-06 4378 :12
Feb-06 4320 :12
Mar-06 5232 :15
Apr-06 4038 :18
May-06 4278 :14

I need to average the seconds for each ID #. I have used the custom format
to make sure it is all in the [h]mm:ss format and I keep getting the #DIV/0!
error. Any suggestions?