| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Newbie Formula Question - how to get formula to repeat in each subsequent row? | New Users to Excel | |||
| formula question... | Excel Discussion (Misc queries) | |||
| Formula question | Excel Discussion (Misc queries) | |||
| Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
| Formula question | Excel Worksheet Functions | |||
Linear Mode
