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
 

=SUMIF($A$1:$A$18, $C$1, $B$1:$B$18)/SUMPRODUCT(--($A$1:$A$18=$C$1),
--($B$1:$B$18<""))
Where A11 is the location you want average.
I hope it will work for you.

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