Prev: If
Next: auto preenchimento validação
From: Dave Peterson on 15 Jan 2010 18:20
=SUM(IF(ISERROR(B1:B10),"",IF((B1:B10=TRUE)*(A1:A10="ibm"),1,"")))
/ COUNTIF(A1:A10,"ibm")

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

carl wrote:
>
> Thanks Dave.
>
> Some of the values in Colb are #NUM! thus the formula returns #NUM!. Can the
> formula be modified to only look at values that are True/False.
>
> Or should the formula in Col B be modified to only return True/False/"Blank" ?
>
> Thanks again.
>
> "Dave Peterson" wrote:
>
> > =countif(a1:a10,"IBM")
> > will give you the denominator
> >
> > =sumproduct(--(a1:a10="IBM"),--(b1:b10=true))
> > will give you the numerator
> >
> > Divide the numerator by the denominator:
> > =sumproduct(--(a1:a10="IBM"),--(b1:b10=true)) / countif(a1:a10,"IBM")
> >
> > And format as a percentage.
> >
> > If you're using xl2007+, there's an =countifs() function you could use, too.
> >
> > About the numerator...
> >
> > Adjust the ranges to match--but you can't use whole columns (except in xl2007+).
> >
> > =sumproduct() likes to work with numbers. The -- stuff changes trues and
> > falsest
> > to 1's and 0's.
> >
> > Bob Phillips explains =sumproduct() in much more detail here:
> > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > And J.E. McGimpsey has some notes at:
> > http://mcgimpsey.com/excel/formulae/doubleneg.html
> >
> > carl wrote:
> > >
> > > Hi.
> > >
> > > My data table looks like this - located in D1:E8
> > >
> > > Stock Condition
> > > IBM TRUE
> > > IBM TRUE
> > > IBM FALSE
> > > IBM TRUE
> > > IBM FALSE
> > > GOOG TRUE
> > > GOOG TRUE
> > >
> > > I am trying to find a formula (B2) for this table - located in A1:B2
> > >
> > > IBM Match
> > > #1 60%
> > >
> > > Where the formula in B2 looks at my data table, finds rows that have the
> > > value in A1, then returns the result of the calculation (# of "TRUE") divided
> > > by (Total Number) - in the case for IBM, 3 divided by 5.
> > >
> > > Thank you in advance.
> >
> > --
> >
> > Dave Peterson
> > .
> >

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: If
Next: auto preenchimento validação