Averaging a column with 0's

[Luna Saisho]

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)

"Luna Saisho" <[email protected]> wrote in message
news:[email protected]...
> 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

[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



"Luna Saisho" <[email protected]> wrote in
message
news:[email protected]...
> 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

[Gary''s Student]

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

[Dave Peterson]

One more:
=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

[Luna Saisho]

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 more:
> =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
>

[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


"Luna Saisho" <[email protected]> wrote in
message
news:[email protected]...
> 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 more:
>> =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
>>

[Luna Saisho]

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" <[email protected]> wrote in
> message
> news:[email protected]...
> > 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 more:
> >> =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
> >>
>
>
>