Arrary formula for Average includes blank cells
"jan" wrote...
...
{=IF(ISERROR(AVERAGE(IF((Delivery!$A$2:$A$35="Mon ticello"),
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 )),"")
--
To top-post is human, to bottom-post and snip is sublime.
|