sumproduct or...?
 

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:G3270),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.

sumproduct or...?
 

Tflight wrote:
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:G3270),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:G3270))/COUNTIF(G6:G327,"0")

sumproduct or...?
 

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:G3270,K6:K327,""))
--
David Biddulph

"Tflight" wrote in message
...
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:G3270),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.

sumproduct or...?
 

Use a array formula:

=AVERAGE(IF(G6:G3270,K6:K327,""))

When you're done typing, press Ctrl+Shift+Enter.
--
Conrad S
#Don't forget to click "Yes" if this post was helpful!#


"Tflight" wrote:

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:G3270),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.

sumproduct or...?
 

=AVERAGE(IF(G6:G3270,K6:K327,""))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200829


"Tflight" wrote:

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:G3270),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.