Big Average Problem

[holmes_w]

Im looking for the Average of B$3:B$85. depending on J=0 and N=0

I know its somethings like the following...

=SUMPRODUCT(--((B$3:B$85)),--($J$3:$J$85=0),--($N$3:$N$85=0))

But instead of the returning the SUM i want the AVERAGE.

Can anyone help?

Thanks.

 

[Andrew Poulsom]

Is it?

=SUMPRODUCT(--(B$3:B$85),--($J$3:$J$85=0),--($N$3:$N$85=0))/SUMPRODUCT(--($J$3:$J$85=0),--($N$3:$N$85=0))

 

[mortgageman]

Im looking for the Average of B$3:B$85. depending on J=0 and N=0

I know its somethings like the following...

=SUMPRODUCT(--((B$3:B$85)),--($J$3:$J$85=0),--($N$3:$N$85=0))

But instead of the returning the SUM i want the AVERAGE.

Can anyone help?

Thanks.

Not tested, but I'm pretty sure =SUMPRODUCT(--((B$3:B$85)),--($J$3:$J$85=0),--($N$3:$N$85=0))/=SUMPRODUCT(--($J$3:$J$85=0),--($N$3:$N$85=0))

should do it.

Gene, "The Mortgage Man", Klein

 

[holmes_w]

Thanks

Thanks. Worked perfectly!!

 

[pgc01]

Hi holmes_w

Also

=AVERAGE(IF($J$3:$J$85=0,IF($N$3:$N$85=0,B$3:B$85)))
Confirmed with CTRL+SHIFT+DEL

HTH
PGC