Average of Sum Product

[JimS]

Can you do an average of a sumproduct?

I have the following formula in a cell. It's for two data points $12
and $10, so the average should be $11, but it returns $22.

=AVERAGE(SUMPRODUCT(($Q$15:$Q$2500)*($V$15:$V$2500="e")))

It seems to be ignoring the average function.

 

[Pete_UK]

Try it like this:

=SUMIF($V$15:$V$2500,"e",$Q$15:$Q$2500) / COUNTIF($V$15:$V$2500,"e")

Hope this helps.

Pete

 

[Bob Phillips]

=AVERAGE(IF($V$15:$V$2500="e",$Q$15:$Q$2500))

this is an array formula, so commit withCtlr-Shift-Enter, not just Enter

 

[JimS]

I ended up using this formula:

=SUMIF($V$15:$V$2500,"s",$N$15:$N$2500)/COUNTIF($V$15:$V$2500,"s")

Thanks to all for the help with the formulas. They both work of
course. One problem, let's say that I get a #DIV/0! error. How can I
make the cell return a value of 0 instead?. I've tried 10 different
iserror methods, but can't get anything to work.

Thanks again

 

[Pete_UK]

Jim,

you will get the #DIV/0 error if this term:

COUNTIF($V$15:$V$2500,"s")

equates to zero. So, you can trap it like this:

=IF(COUNTIF($V$15:$V$2500,"s")=0,0,SUMIF($V$15:$V$2500,"s",$N$15:$N
$2500)/COUNTIF($V$15:$V$2500,"s"))

Hope this helps.

Pete

 

[JimS]

Ahhh, that makes perfect sense. I didn't think to find the part that
would create the error. I assumed it was because the whole formula
was dividing by zero.

Thanks again,

Jim

 

[Pete_UK]

You're welcome, Jim - thanks for feeding back.

Pete

 

[Harlan Grove]

you will get the #DIV/0 error if this term:

COUNTIF($V$15:$V$2500,"s")

equates to zero. So, you can trap it like this:

=IF(COUNTIF($V$15:$V$2500,"s")=0,0,SUMIF($V$15:$V$2500,"s",
$N$15:$N$2500)/COUNTIF($V$15:$V$2500,"s"))

....

If you're going to return zero when COUNTIF equals zero, simpler and
shorter to use

=SUMIF($V$15:$V$2500,"s",$N$15:$N$2500)/MAX(1,COUNTIF($V$15:$V
$2500,"s"))