Arrary formula for Average includes blank cells

[jan]

I am using an Average formula (array) on a summary
worksheet to return the average of a set a data from
another worksheet (detail) based on location.

The worksheet data (detail) is set up as follows; however
there are several locations in the entire worksheet. When
I use the subtotal function with an autofilter set my data
for the account shown below is correct. The Average
function under "Other Deliveries" does not include the
cells with no values.

Location Account Month/Year
Stat Routine Other
Del Del Del
Monticello ABC vendor Apr 04 6
Monticello ABC vendor Apr 04 7 7
Monticello ABC vendor Apr 04 7 6
Monticello DEF Vendor Apr 04 7 6
Monticello DEF Vendor Apr 04 1 1 1
Monticello Spice of Life Apr 04 3 5 2
Average 5.0 5.2 1.5

On the summary worksheet where I have used an array
formula to average, it does include the blank cells and
returns a different result. Here is the array formula
that is entered to return the detail info for "Other del"
for the location Monticello.

{=IF(ISERROR(AVERAGE(IF((Delivery!
$A$2:$A$35="Monticello"),Delivery!$G$2:$G$35))),"",AVERAGE
(IF((Delivery!$A$2:$A$35="Monticello"),Delivery!
$G$2:$G$35)))}

Delivery Scores
Locations Stat Routine Other
Bettendorf 6.5 5.5 6.5
Fort Wayne 6.5 6.0 6.5
Menomonie 7.0 7.0 7.0
Monticello 4.2 5.2 0.5

I need help to revise the formula to return the average
result as shown on the detail worksheet. Although the
first criteria of the formula exists (Location), I do not
want the formula to calculate blank cells. Can someone
help me.

TIA

 

[Frank Kabel]

Hi
normally AVERAGE would ignore real blank cells automatically

 

[Guest]

Jan

Interesting problem. I noticed something odd with the array formula. Try the following

=AVERAGE(IF($A$2:$A$13="monticello",IF(D$2$13="","",D213),"")

This basically replaces "" with "" -- which certainly seems ridiculous. But it works on my machine

Art,

 

[Harlan Grove]

...
...

{=IF(ISERROR(AVERAGE(IF((Delivery!$A$2:$A$35="Monticello"),
Delivery!$G$2:$G$35))),"",AVERAGE(IF((Delivery!$A$2:$A$35="Monticello"),
Delivery!$G$2:$G$35)))}

...

The problem lies with IF. The IF function when called with an array first
argument doesn't/can't return range references as results. Instead, it returns
arrays, and in such arrays blank cells are converted to numeric zeros.

You need to use something like

=IF(COUNTIF(Delivery!$A$2:$A$35,"Monticello"),
AVERAGE(IF((Delivery!$A$2:$A$35="Monticello")
*ISNUMBER(Delivery!$G$2:$G$35),Delivery!$G$2:$G$35)),"")

 

[Jan]

Art,

The formula you provided does work. But there is another
issue I neglected to mention in my initial message. I have
not been able to revise your formula to get it to work.

The Summary worksheet is set up to show all locations. If
a specific location is not in the detail worksheet (for
the month) then the formula returns the Div/# error.

How can I adjust the formula that if the location is not
in the detail worksheet then "do nothing" in the cell.

TIA

-----Original Message-----
Jan,

Interesting problem. I noticed something odd with the

array formula. Try the following:

=AVERAGE(IF($A$2:$A$13="monticello",IF (D$2$13="","",D213),""))

This basically replaces "" with "" -- which certainly

seems ridiculous. But it works on my machine.

 

[Frank Kabel]

Hi
one way:
=IF(ISERROR(AVERAGE(....),"",AVERAGE(...))