Averaging a column with 0's

[Guest]

Hiya!

I've been tasked to fix a spreadsheet that has a few issues. I've fixed
most of what I need, but I have a problem I'm wondering if anyone has a
solution to.

Basically, it needs to find an average of a rather tall column of times.
The problem is that until a truck has come to pick up the load, the times are
all 0:00, and from what I know, it's averaging those zeros. Does anyone know
how to adapt the averaging forumula (which is currently =AVERAGE(O38:O167))
to forget about the zero'd times?

Thanks!!
Steph

 

[Bob Phillips]

=AVERAGE(IF(O38:O167<>0,O38:O167))

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


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Chip Pearson]

The following array formula will exclude zeros from the
AVERAGE.of the numbers in A1:A10.

=AVERAGE(IF(A1:A10<>0,A1:A10,FALSE))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the formula and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {} in the formula
bar.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...

 

[Guest]

If the truck has not yet arrived, then leave the cell blank rather than zero.
AVERAGE() will ignore blanks.

 

[Dave Peterson]

One more:
=sum(o38167)/countif(o38167,"<>"&0)

As long as you always have some sort of numbers in that range.

 

[Guest]

Wow! I go have something to eat and four responses! Thank you to all!

Unfortunately, leaving the space blank would be too easy for something I
generally have to work on. ^_^ That number is there by result of checking
the time the truck starts to be loaded and when it leaves.

CTRL+SHIFT+ENTER is no problem, but I have yet to hear about an array
forumula... I'm going to have to research this, as I think I might be missing
something big.

BTW, it seems to work great! I didn't get any {}'s, however.

Thanks again!
Steph

 

[Chip Pearson]

Array formulas are a powerful tool in Excel. See
http://www.cpearson.com/excel/array.htm for some details and
examples.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message

 

[Guest]

What a strange coincidence that the domain name and your name are so similar!


Seriously, you wrote a fantastic page that is going to help me write some
great add-ons for what I'm working on. It'll be nice to give this back to
them not only fixed, but improved.

Thanks again! ^_^
Steph