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

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

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

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

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