sumproduct or...?

  • Thread starter Thread starter Tflight
  • Start date Start date
T

Tflight

Hi,

I am trying to find the average of the total number for all the cells that
in their row have a number that is greater than 0 in one of the categories.

The formula I sort of have is:
IF(G6:G327>0),AVERAGE(K6:K327)

I just learned about the function sumproduct, but I'm not sure if it would
work in this case. I don't really understand the function anyhow. Any help
would be appreciated.
 
Tflight said:
Hi,

I am trying to find the average of the total number for all the cells that
in their row have a number that is greater than 0 in one of the categories.

The formula I sort of have is:
IF(G6:G327>0),AVERAGE(K6:K327)

I just learned about the function sumproduct, but I'm not sure if it would
work in this case. I don't really understand the function anyhow. Any help
would be appreciated.
Click to expand...


Try this:

=SUMPRODUCT(K6:K327*(G6:G327>0))/COUNTIF(G6:G327,">0")
 
Use a array formula:

=AVERAGE(IF(G6:G327>0,K6:K327,""))

When you're done typing, press Ctrl+Shift+Enter.
 
=AVERAGE(IF(G6:G327>0,K6:K327,""))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
 
Back
Top