sumproduct or...?

[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.

 

[Glenn]

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.

Try this:

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

 

[David Biddulph]

There are a few concepts that you need to think about.

Firstly, you are trying to calculate the average, so the AVERAGE should be
the outer function, with the IF providing the input.
Secondly, you need to remind yourself (using Excel help if necessary) of the
syntax of the IF function.
Thirdly, what you need is an array function, so it needs to be entered with
Control Shift Enter.

Try the array formula =AVERAGE(IF(G6:G327>0,K6:K327,""))

 

[Conrad S]

Use a array formula:

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

When you're done typing, press Ctrl+Shift+Enter.

 

[Gary''s Student]

=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.