Formula Question

[carl]

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.

 

[ryguy7272]

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

Slight modification:
=SUMPRODUCT((D11949="IBM")*(E1:E1949=TRUE))/SUMPRODUCT(--(D11949="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(--(D11949=A1),--(E1:E1949=A2))/SUMPRODUCT(--(D11949=A1))

 

[Don Guillett]

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.

 

[Dave Peterson]

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

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]

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