average

G

ggff

I would like to calculate the aveage of a column:

=AVERAGE(A002:A298)

The column is full of "time" formulas that are calculated from two
other columns. Because of incomplete date these calculations are not
all complete. I would like to average only the columns where there is
a time in it.

Thanks in advance.
 
P

Peo Sjoblom

That is how average works, it disregards blanks, if there are zero time as
in 00:00 you can use

=AVERAGE(IF(A1:A50<>0,A1:A50))

entered with ctrl + shift & enter
 
G

ggff

Thank-you for the response.

When I get blanks the average works fine. But I sometimes get
"##########" or "#VALUE!". I assume these are because something along
the line was entered incorrectly. It is not the type of thing I can
go back and re-enter correctly. So, I guess what I am wondering is,
is it possible to have the average also ignore the "##########" and
"#VALUE!"


But these are not always blanks. Sometimes I get an error message
because the data was not entered correctly or On Tue, 14 Oct 2003
 
T

Tom Ogilvy

=AVERAGE(IF((A1:A50=0)+iserror(A1:A50)+(Left(A1:A50,1)="#"),False,A1:A50))
Entered with Ctrl+Shift+Enter
 
G

ggff

=AVERAGE(IF((A1:A50=0)+iserror(A1:A50)+(Left(A1:A50,1)="#"),False,A1:A50))
Entered with Ctrl+Shift+Enter
Click to expand...

What does this mean?

Entered with Ctrl+Shift+Enter

Thanks.
 
P

Peo Sjoblom

Of course that won't work if you have zeros,

=SUMIF(A1:A10,"<>#VALUE!")/(COUNTIF(A1:A10,">0")+COUNTIF(A1:A10,"<0"))
 
P

Peo Sjoblom

Note that Tom's formula only works if the ######### is a text string,
if it is due to negative time it won't work. My first amendment formula
works if there
are no zeros and the last amendment should work if the only options are time
(be it negative or positive),
#VALUE! error and zeros, if you have other errors like REF or DIV/0 then it
will fails

Ctrl shift enter means what it says, type in the formula or copy it into a
cell,
then instead of pressing enter you press ctrl + shift & enter at the same
time,
it should put a curly bracket around the formula { formula }
 
C

Chip Pearson

It means to hold down the Ctrl key, the Shift key and then hit the Enter
key, rather than just pressing the Enter key alone. This will "array enter"
the formula. If you do this properly, Excel will display the formula
enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Pearson Software Consulting, LLC
Microsoft MVP - Excel
www.cpearson.com [email protected]
 
M

Marian

ggff napsal(a):
I would like to calculate the aveage of a column:

=AVERAGE(A002:A298)

The column is full of "time" formulas that are calculated from two
other columns. Because of incomplete date these calculations are not
all complete. I would like to average only the columns where there is
a time in it.

Thanks in advance.
Click to expand...


Suppose you have data in area A1:A18. In A20 use formula:
=SUM(A1:A18)/COUNTIF(A1:A18;">0").

Marian
 
P

Peo Sjoblom

Won't work since it will return an error, also shouldn't negative numbers be
included?

if only positive numbers

=SUMIF(A1:A10,"<>#VALUE!")/(COUNTIF(A1:A10,">0"))
 
Top