software

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:ca5000gt;0),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0))
--Bruce

quot;ellebellequot; wrote:

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

that worked a treat - thanks!

quot;bpeltzerquot; wrote:

gt; You could use sumproduct to add the appropriate cells and to the count the
gt; appropriate cells, then just divide the two:
gt; =sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0),(ca1:ca5000)) /
gt; sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0))
gt; --Bruce
gt;
gt; quot;ellebellequot; wrote:
gt;
gt; gt; I want to get the average with two criteria. if the cells in column Z = B11
gt; gt; then I want the average value from column CA (BUT I only want the average of
gt; gt; 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.quot;bpeltzerquot; wrote:

gt; You could use sumproduct to add the appropriate cells and to the count the
gt; appropriate cells, then just divide the two:
gt; =sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0),(ca1:ca5000)) /
gt; sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0))
gt; --Bruce
gt;
gt; quot;ellebellequot; wrote:
gt;
gt; gt; I want to get the average with two criteria. if the cells in column Z = B11
gt; gt; then I want the average value from column CA (BUT I only want the average of
gt; gt; cells greater than zero.)

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Hanr3quot; gt; wrote in message
news
gt; What do the -- signs within the formula represent, or do?
gt; --
gt; Life is an adventure, are you living it?
gt;
gt; These are just my opinions, please feel free to correct them if they are
gt; wrong.
gt;
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; You could use sumproduct to add the appropriate cells and to the count
the
gt; gt; appropriate cells, then just divide the two:
gt; gt; =sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0),(ca1:ca5000)) /
gt; gt; sumproduct(--(z1:z5000=b11),--(ca1:ca5000gt;0))
gt; gt; --Bruce
gt; gt;
gt; gt; quot;ellebellequot; wrote:
gt; gt;
gt; gt; gt; I want to get the average with two criteria. if the cells in column Z
= B11
gt; gt; gt; then I want the average value from column CA (BUT I only want the
average of
gt; gt; gt; cells greater than zero.)