Criteria average ignoring blanks
 

Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?

Criteria average ignoring blanks
 

Puk,

=AVERAGE(B2:B200) (or whatever).

The AVERAGE function ignores empty cells, but averages a zero as zero, which, I think, is
what you want.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"flumpuk" wrote in message
oups.com...
Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?

Criteria average ignoring blanks
 

On 16 Oct, 22:59, "Earl Kiosterud" wrote:
Puk,

=AVERAGE(B2:B200) (or whatever).

The AVERAGE function ignores empty cells, but averages a zero as zero, which, I think, is
what you want.
--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------"flumpuk" wrote in message

oups.com...



Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?- Hide quoted text -

- Show quoted text -

What I want to do is average colum B but only when a set location is
in its adjacant column A

Criteria average ignoring blanks
 

On 16 Oct, 22:59, "Earl Kiosterud" wrote:
Puk,

=AVERAGE(B2:B200) (or whatever).

The AVERAGE function ignores empty cells, but averages a zero as zero, which, I think, is
what you want.
--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------"flumpuk" wrote in message

oups.com...



Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?- Hide quoted text -

- Show quoted text -

WHat I want to do is average column B but only when a set location is
in Column A. Something like =AVERAGE ((B:B)IF a1 = "Location")

Criteria average ignoring blanks
 

Try this *array* formula:

=AVERAGE(IF((A1:A100="Location")*(B1:B100<""),B1: B100))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

Array formulas *cannot* use entire column references (A:A, B:B).

Also, you could assign a cell to contain the criteria for Column A.
Say you enter
Location
in C1, then the formula would be:

=AVERAGE(IF((A1:A100=C1)*(B1:B100<""),B1:B100))

Where you could change the criteria without having to change the formula
itself.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"flumpuk" wrote in message
oups.com...
On 16 Oct, 22:59, "Earl Kiosterud" wrote:
Puk,

=AVERAGE(B2:B200) (or whatever).

The AVERAGE function ignores empty cells, but averages a zero as zero,
which, I think, is
what you want.
--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------"flumpuk"
wrote in message

oups.com...



Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?- Hide quoted text -

- Show quoted text -

WHat I want to do is average column B but only when a set location is
in Column A. Something like =AVERAGE ((B:B)IF a1 = "Location")

Criteria average ignoring blanks
 

=SUMIF($A$2:$A$8, "Location",
Sheet2!$B$2:$B$8)/SUMPRODUCT(--($A$2:$A$8="Location"),
--(Sheet2!$B$2:$B$8<0))

"flumpuk" wrote:

Hi

Further to my post from yesterday I now have another question

Coulmn A is a list of locations
Coulmn B is a figure

Sometimes Column B is empty as location was not online and no data
received

I need to average out the numbers in column B when a set location is
in column A. I need to disregard column B if it is blank . A zero is a
valid value in this Column.

How do I do this in Excel 2000?