Formula Question
in [Functions]
|
Prev: If
Next: auto preenchimento validação
From: carl on 15 Jan 2010 10:25 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.
From: ryguy7272 on 15 Jan 2010 10:50 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.
From: Don Guillett on 15 Jan 2010 10:53 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 dguillett(a)gmail.com "carl" <carl(a)discussions.microsoft.com> wrote in message news:5F078D40-5E46-4EC4-8AE2-89D6DD41F99E(a)microsoft.com... > 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.
From: Dave Peterson on 15 Jan 2010 10:54 =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
From: carl on 15 Jan 2010 15:51
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 > . > |