# Big Average Problem

#### holmes_w

##### New Member
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.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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))

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

Thanks

Thanks. Worked perfectly!!

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

Replies
13
Views
617
Replies
2
Views
62
Replies
24
Views
686
Replies
8
Views
318
Replies
1
Views
94

1,217,498
Messages
6,136,997
Members
450,037
Latest member
Tao86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back