average

  • Thread starter Thread starter ggff
  • Start date Start date
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.
 
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
 
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
 
=AVERAGE(IF((A1:A50=0)+iserror(A1:A50)+(Left(A1:A50,1)="#"),False,A1:A50))
Entered with Ctrl+Shift+Enter
 
=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.
 
Of course that won't work if you have zeros,

=SUMIF(A1:A10,"<>#VALUE!")/(COUNTIF(A1:A10,">0")+COUNTIF(A1:A10,"<0"))
 
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 }
 
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 (e-mail address removed)
 
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
 
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"))
 
Back
Top