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

=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)

"Luna Saisho" wrote in message
...
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

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



"Luna Saisho" wrote in
message
...
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

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


"Luna Saisho" wrote:

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

One mo
=sum(o38:o167)/countif(o38:o167,"<"&0)

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

Luna Saisho wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

One mo
=sum(o38:o167)/countif(o38:o167,"<"&0)

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

Luna Saisho wrote:

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

--

Dave Peterson

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


"Luna Saisho" wrote in
message
...
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

"Dave Peterson" wrote:

One mo
=sum(o38:o167)/countif(o38:o167,"<"&0)

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

Luna Saisho wrote:

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

--

Dave Peterson

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

"Chip Pearson" wrote:

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


"Luna Saisho" wrote in
message
...
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

"Dave Peterson" wrote:

One mo
=sum(o38:o167)/countif(o38:o167,"<"&0)

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

Luna Saisho wrote:

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

--

Dave Peterson