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

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:ca5000>0),(ca1:ca5000)) /
sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0))
--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:ca5000>0),(ca1:ca5000)) /
> sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0))
> --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:ca5000>0),(ca1:ca5000)) /
> sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0))
> --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" <[email protected]> wrote in message
news:[email protected]...
> 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:ca5000>0),(ca1:ca5000)) /
> > sumproduct(--(z1:z5000=b11),--(ca1:ca5000>0))
> > --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.)