Criteria average ignoring blanks

[flumpuk]

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?

 

[Earl Kiosterud]

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]

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...










- Show quoted text -

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

 

[flumpuk]

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...










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

 

[RagDyer]

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.

 

[Guest]

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