average with mulitple ciriteria
 

I want to get the average with two criteria. if the cells in column Z = B11
then I want the average value from column CA (BUT I only want the average of
cells greater than zero.)

average with mulitple ciriteria
 

You could use sumproduct to add the appropriate cells and to the count the
appropriate cells, then just divide the two:
=sumproduct(--(z1:z5000=b11),--(ca1:ca50000),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca50000))
--Bruce

"ellebelle" wrote:

I want to get the average with two criteria. if the cells in column Z = B11
then I want the average value from column CA (BUT I only want the average of
cells greater than zero.)

average with mulitple ciriteria
 

that worked a treat - thanks!

"bpeltzer" wrote:

You could use sumproduct to add the appropriate cells and to the count the
appropriate cells, then just divide the two:
=sumproduct(--(z1:z5000=b11),--(ca1:ca50000),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca50000))
--Bruce

"ellebelle" wrote:

I want to get the average with two criteria. if the cells in column Z = B11
then I want the average value from column CA (BUT I only want the average of
cells greater than zero.)

average with mulitple ciriteria
 

What do the -- signs within the formula represent, or do?
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


"bpeltzer" wrote:

You could use sumproduct to add the appropriate cells and to the count the
appropriate cells, then just divide the two:
=sumproduct(--(z1:z5000=b11),--(ca1:ca50000),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca50000))
--Bruce

"ellebelle" wrote:

I want to get the average with two criteria. if the cells in column Z = B11
then I want the average value from column CA (BUT I only want the average of
cells greater than zero.)

average with mulitple ciriteria
 

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Hanr3" wrote in message
...
What do the -- signs within the formula represent, or do?
--
Life is an adventure, are you living it?

These are just my opinions, please feel free to correct them if they are
wrong.


"bpeltzer" wrote:

You could use sumproduct to add the appropriate cells and to the count
the
appropriate cells, then just divide the two:
=sumproduct(--(z1:z5000=b11),--(ca1:ca50000),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca50000))
--Bruce

"ellebelle" wrote:

I want to get the average with two criteria. if the cells in column Z
= B11
then I want the average value from column CA (BUT I only want the
average of
cells greater than zero.)