Average more than 30 numbers

[James Auty]

I am trying to average more than 30 non-contigious cells
that contain time [hh:mm:ss] some of which may be zero
[not blank]. If I try to sum the cells then divide by
the count of the cells [the count function identifies the
correct number] I get an answer that is obviously not
correct.

Is there a way to use the average function to acomplish
this task for more than 30 numbers?

Thanks

James

 

[Pete McCosh]

James,

in what way is your output wrong? It may simply need to be
formatted as time, rather than number or general.

Pete

 

[James Auty]

Pete

I have, in this instance, 48 cells each with a time [some
zero] that add up to just over 17 minutes. The highest
value is about 45 secs and a manual calcluation gives an
average of 22 secs. Selecting average in the status bar
also gives 22 secs as an average. If I add up the cells
then I get the correct answer. If I count the cells I
also get a correct answer. If I divide the sum by the
count I get about 9 mins, obviously wrong.

I have checked the formatting and they are all formatted
as hh:mm:ss. Changing this does not alter the result

'Average' seems to work but the function is limited to 30
numbers. can we get round this limitation?

James

-----Original Message-----
James,

in what way is your output wrong? It may simply need to be
formatted as time, rather than number or general.

Pete

-----Original Message-----
I am trying to average more than 30 non-contigious cells
that contain time [hh:mm:ss] some of which may be zero
[not blank]. If I try to sum the cells then divide by
the count of the cells [the count function identifies the
correct number] I get an answer that is obviously not
correct.

Is there a way to use the average function to acomplish
this task for more than 30 numbers?

Thanks

James
.

.

 

[Paul]

Pete

I have, in this instance, 48 cells each with a time [some
zero] that add up to just over 17 minutes. The highest
value is about 45 secs and a manual calcluation gives an
average of 22 secs. Selecting average in the status bar
also gives 22 secs as an average. If I add up the cells
then I get the correct answer. If I count the cells I
also get a correct answer. If I divide the sum by the
count I get about 9 mins, obviously wrong.

I have checked the formatting and they are all formatted
as hh:mm:ss. Changing this does not alter the result

'Average' seems to work but the function is limited to 30
numbers. can we get round this limitation?

James

SUM/COUNT is the way to go. Are you literally saying that you have the SUM
in one cell (giving just over 17 minutes) and the COUNT in another (giving
what?), and dividing one cell by the other results in about 9 minutes? For
further help, post the actual formulas you are using.

 

[mnewdick]

Highlight every cell (using CTRL) and name the range. Then perform your
calculations against the range name.

 

[James Auty]

Pete

I have looked at the formula I was using and realsied
that the brackets were in the wrong place. It new works:

=(sum(nums 1-30)+ sum(nums 31-48))/(count(nums 1-30)+count
(nums 31-48))

However, is there a way to minimise the need to select so
many cells so many times, maybe using the average
function?

James

-----Original Message-----

Pete

I have, in this instance, 48 cells each with a time [some
zero] that add up to just over 17 minutes. The highest
value is about 45 secs and a manual calcluation gives an
average of 22 secs. Selecting average in the status bar
also gives 22 secs as an average. If I add up the cells
then I get the correct answer. If I count the cells I
also get a correct answer. If I divide the sum by the
count I get about 9 mins, obviously wrong.

I have checked the formatting and they are all formatted
as hh:mm:ss. Changing this does not alter the result

'Average' seems to work but the function is limited to 30
numbers. can we get round this limitation?

James

SUM/COUNT is the way to go. Are you literally saying that you have the SUM
in one cell (giving just over 17 minutes) and the COUNT in another (giving
what?), and dividing one cell by the other results in about 9 minutes? For
further help, post the actual formulas you are using.


.

 

[Harlan Grove]

I am trying to average more than 30 non-contigious cells
that contain time [hh:mm:ss] some of which may be zero
[not blank]. If I try to sum the cells then divide by
the count of the cells [the count function identifies the
correct number] I get an answer that is obviously not
correct.

Is there a way to use the average function to acomplish
this task for more than 30 numbers?

=AVERAGE(A1,B2,C3,...,AC29,AD30,AE31)

is an AVERAGE call with 30 arguments, so not going to work. However,

=AVERAGE((A1,B2,C3,...,AC29,AD30,AE31))

is an AVERAGE call with *one* multiple area range argument, (A1, B2, C3, ...,
AC29, AD30, AE31), so it works just fine.