Ignoring blank cells on getting an average

[Guest]

Hi

I have four columns of data A,B,C and D. I would like to get the average
from the results in column D. But only when it matches certain criteria in
A,B and C.

I put
{=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon")*($C$1:$C$100=1),$D$1:$D$100))}

This works ok but sometimes when i have this data in there for info in other
columns such as E,F. The D will be blank but this will affect the average.
Can i add something to ignore the blanks cells.

Many thanks

 

[Guest]

Try:

=AVERAGE(IF(($A$1:$A$100="R1")*($B$1:$B$100="Mon")*($C$1:$C$100=1),ISNUMBER($D$1:$D$100)),$D$1:$D$100)

 

[Dave Peterson]

Maybe just adding more criteria would be ok:

=AVERAGE(IF(($A$1:$A$100="R1")
*($B$1:$B$100="Mon")
*($C$1:$C$100=1)
*ISNUMBER($D$1:$D$100), $D$1:$D$100))

 

[Guest]

You can use this formula:

=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/(COUNTIF($B$25:$E$28,($B$25:$B$28="r1")*($C$25:$C$28="Mon")*($D$25:$D$28=1))-IF(($B$25:$B$28="r1")*($C$25:$C$28="mon")*($D$25:$D$28=1),COUNTBLANK($E$25:$E$28),0))

tell us if it works

 

[Guest]

you can use this formula:

=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/(COUNTIF($B$25:$E$28,($B$25:$B$28="r1")*($C$25:$C$28="Mon")*($D$25:$D$28=1))-IF(($B$25:$B$28="r1")*($C$25:$C$28="mon")*($D$25:$D$28=1),COUNTBLANK($E$25:$E$28),0))

tell us if it works.

 

[Guest]

Many thanks

They worked fine. It was a big help!

 

[Guest]

You can use a small form of this formula also..
=SUMPRODUCT(--($B$25:$B$28="r1"), --($C$25:$C$28="mon"),
--($D$25:$D$28=1)*($E$25:$E$28))/SUMPRODUCT(--($B$25:$B$28="r1"),
--($C$25:$C$28="mon"), --($D$25:$D$28=1)*($E$25:$E$28>0))