Formula Question
 

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.

Formula Question
 

Try this in cell B2:
=SUMPRODUCT(--(D1:D1949="IBM"),--(E1:E1949=TRUE))/SUMPRODUCT(--(D1:D1949="IBM"))

Slight modification:
=SUMPRODUCT((D1:D1949="IBM")*(E1:E1949=TRUE))/SUMPRODUCT(--(D1:D1949="IBM"))

See this site for a great description of how sumproduct works:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You may want to try IBM in cell A1 and True in cell A2, an dthis use this
function:
=SUMPRODUCT(--(D1:D1949=A1),--(E1:E1949=A2))/SUMPRODUCT(--(D1:D1949=A1))
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


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

Formula Question
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"carl" wrote in message
...
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.

Formula Question
 

=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 he
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

Formula Question
 

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

Formula Question
 

=SUM(IF(ISERROR(B1:B10),"",IF((B1:B10=TRUE)*(A1:A1 0="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 he
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