Formula Question 01-15-10

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.
0
Utf
1/15/2010 3:25:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
1142 Views

Similar Articles

[PageSpeed] 53

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.
0
Utf
1/15/2010 3:50:02 PM
      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@gmail.com
"carl" <carl@discussions.microsoft.com> wrote in message 
news:5F078D40-5E46-4EC4-8AE2-89D6DD41F99E@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. 

0
Don
1/15/2010 3:53:41 PM
=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
0
Dave
1/15/2010 3:54:59 PM
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
> .
> 
0
Utf
1/15/2010 8:51:01 PM
=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
0
Dave
1/15/2010 11:20:32 PM
Reply:

Similar Artilces:

Question about cell assignment
I am making a spreadsheet where I add one new data point to a colum each day (e.g. number of cars I honked at yesterday) I also have a box that calculates statistics based on the data entere (e.g. total number of cars, percent increase over time, etc) How can I assign a formula in the "statistics box" that will updat when I enter in the new value in the "data column"? Is there som general notatation that will tell the cell to search for the mos recent value in a column of data? Thanks for your comments -- Message posted from http://www.ExcelForum.com One formula woul...

Pivot table question #4
Hello all, I am working on a Excel data table. The 3 columns are year(i.e. 2011), dollar amount, purchase dollar amount. I have been asked to group by year in the following buckets. 0-2 years 2-3 years 3-5 years 5-10 years 10+ years I seem to be able to group by 2 year intervals, 3 year intervals etc, but cannot seem to group as I have been asked. That is 2 years, then 1 year, then 2 years, then 5 years. Any help would be greatly appreciated. Thank you! TimD On Feb 10, 7:36=A0pm, tdkooch <malor...@gmail.com> wrote: > Hello all, > I am working on a Excel data table. =A0The 3...

schema question for dynamic elements
I was wondering how to write the schema so that the DataSet LoadXml() method will create the associated DataTable. I've tried the following in the schema file (using VS.NET 2005): <xs:complexType name="Header"> <xs:complexContent> <xs:restriction base="xs:anyType"> <xs:sequence> <xs:any /> </xs:sequence> </xs:restriction> </xs:complexContent> </xs:complexType> If I don't load the schema (in the snippet below), the "Header" table is created. I want it to be dynamic ...

question about watermarks and text (non-wrap)
I am creating small labels, and have created a watermark, but can't figure out how to place text directly over the watermark. I have tried the front to back option, as well as some of the options for photo format, but each time I try to do this the text slides around the photo. If you can help, please email me at: cynthia@peconicbaywinery.com Thank you! Create the text box. Now make the text box transparent by using the Cntrl T keys. Drag the text box over the watermark, if not already there. That's it. -- Don Vancouver, USA "Cynthia" <Cynthia@discussions.microsoft....

Formula Error
I am using the following formula, which works really well and brings back the data that I want it to: =INDEX(Data!$B$13:$B$35,MATCH(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5),Data!$A$13:Data!$A$35,0)) However, when I close the file and reopen it, an error #N/A comes up in the cell. Once I click in the formula and hit return the proper value comes back in. Is there any way to stop this error keep coming up and keep the proper value in it. Thanks. ...

Conditional Formatting 05-06-10
Hi and TIA. I have a worksheet like so. I'm trying to conditionally format the cells in Column C. If this is possible where do a place a call to the procedure? I want the procedure to run for each individual row except I can't simply copy the formula down the sheet because I have headers and totals rows. I'm new to excel. I'm an Access geek. Any advice or if you can point me in the right direction is appreciated. Thanks for your time! Header: USS Vinson A B C 1 3 3 1 2 2 3 4 3 1 2 2 Tot 6 8 ...

Outlook XP
We are running Outlook XP on several computers in a home network that includes an external hard drive that is connected directly to the hub. Is there a way to run a calendar data file on the external drive that can be seen/used by multiple users on the home network? Thanks, sct no, outlook doesn't support network use and pst can only be used by one outlook at a time. more infor and other options are here: http://www.slipstick.com/outlook/share.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual Quic...

SDK question
I've done a bunch of work with the SDK but can't seem to get add a campaign response, I've verified that my credentials are fine as I can perform a lot of other functions but can't nail this one down. Is there anyway to get a more meaningful error message than "Server can't process request"? Here's my code: Dim service As New CrmService Dim myCreds As System.Net.NetworkCredential = New System.Net.NetworkCredential myCreds = GetMyCredentials() service.Credentials = myCreds ' Get Current User Id D...

Yet another file compatibility/conversion question (2007 <--> 2000
I've searched this forum but can't find a solution to my specific problem. Sorry if I missed something. An associate of mine, who runs Publisher 2000, is getting the error message "Publisher cannot open this file. This is not a Publisher file.". The file was created at her Office in Publisher 2007 and saved using the "Save As...Publisher 2000" function. And any files she creates cannot be opened on the Office PC's that all have Publisher 2007 & MS Office 2007. I know there is a Compatibility Pack for Office 2007, but I don't know if her Office peop...

Question about client backups in 2010
I’m very interested in the new client backups from DPM 2010, does anyone know exactly how the backup system will work. From what I understand you can set path and file types not to be backed up, but then does it work on a normal/incremental type backup, or block level. Will the “full” backup only happen one time, and then all future backups be block levels that get applied to full when the retention is up? Basically will it work how DPM works with servers, as in all the data won’t go over the wire after the first full backup? Full backup happens only at one time, all l...

Struggling with date formulas
Hi everyone. Using E02 on XP. I am trying to work some formulas and I have the general idea what I need to do. I have a date of birth [DOB] and date of hire [DOH]. I need to calculate Eligibility to Participate by calculating [Age] and [Service] and then: If [Age]>21 AND [Service]>1, name the next occuring 01/01 or 07/01 (with the year). In other words, once you attain age 21 and have at least 1 year of service, you are eligible to enroll on the next occuring January 1 or July 1. The ultimate question is "What is the Date of Eligibility?" If DOB is 05/01/1960 and ...

IF formula question #3
Ok. All I want to do is to say if Col A = "Yes" or Col B = "Yes" I want a Yes printed in Col C. I can't seem to figure this one out. Thanks for your assistance. In C1 =if(or(A1="Yes",B1="Yes"),"Yes","") "Mad Dog" wrote: > Ok. All I want to do is to say if Col A = "Yes" or Col B = "Yes" I want a > Yes printed in Col C. I can't seem to figure this one out. > > Thanks for your assistance. > > > Hi Try something like: =IF(OR(A2="Yes",B2="Yes&quo...

Formula for Date and Time duration
Morning All, I have to work out the time in which a case has taken to resolve on our system. Now in Excel I have the Case Date (Date opened), Case Time (Time opened), Closure Date, Closure Time. Does anyone have a formula that works out the Number of Days and Time it has taken to resolve a case? This needs to not include weekends and the working hours 08:30 to 18:30. This is way beyond my knowledge but I'm sure it can be done, I'm hoping someone has a formula for this already. -- Adam ----------- Windows 98 + Office Pro 97 Evening Adam! I have Date Opened in A1 Time Opened in ...

Date Difference 02-24-10
Good Morning. I am trying to figure out how to calculate the number of days between two dates. I have a table callled events. That table has event date, event type, event outcome. For example a defendant will have an Advisement Date (AA as stored in the table) and say a Preliminary Hearing date (PH). When i enter information I will enter the date, event type and the event outcome will be (PD for Pending, CN for Continued, or CP for completed). For example Case Number 10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary Hearing on 02/05/10 which was C...

Those blo**y Formulas
HI All - I have creatsed a list of creditors in column a that vary from a-z. i have got 3 or 4 diffrent suppliers for each letter and and each supplier is totaled. Iwould like a formula that sum up only the total of each individual supplier. Is this possibile.??? Help greatly appreciated Thanks -- jhill ------------------------------------------------------------------------ jhill's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23274 View this thread: http://www.excelforum.com/showthread.php?threadid=381153 sumif! if your suppliers are in say A1:a50,and you...

Another CTabCtrl question.....
Hello guys, I have one more question relating to nexted CTabCtrls. I got some great information from Joe, and I have been able to next the CTabCtrls although I believe I hurt myself getting it to work. I am now in a position where I would like to change the text on a button on the main dialog from within the nested CTabCtrl dialog. I see two options here (maybe there are others). First, I could pass a pointer to the button control all the way down the nested control structures, as I am creating the nested controls. This would be explicit, so easy to follow for future maintenance,...

Can't install 12.2.1 on any computers 10.4-10.6
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel "You cannot install Office 2008 12.2.1 Update on this volume. A version of the software required to install this update was not found on this volume." I get this error on every computer in the building, PowerPC computers running 10.4 and Intel computers running 10.4, 10.5 and 10.6. All the computers currently have 12.2.0 and have all current OS updates. Any ideas? On 10/1/09 7:54 AM, in article 59b7db0d.-1@webcrossing.caR9absDaxw, "macinmypocket@officeformac.com" <macinmypocket@officeformac.com...

Generating Unicode characters in a cell using formulas
With older multibyte Chinese code sets like Big5, I could create a large list or table of Chinese characherts by generating their two components with a formula like =CHAR(HEX2DEC(LEFT(A5,2)))&CHAR(HEX2DEC(RIGHT(A5,2))) were the hex code for the character (eg. A440) was in tyhe target cell A5. With Unicode, all I get are the two individual characters that (in Big5 anyway) made up the multi-byte code for a character. BTW: In the case above, Big5 A440 is Unicode U+4e00, the character for "one" 一. Hi Geoff, You will have to create a User Defined Function for use on your w...

"What-if" formula for either a manual entry and automated entry
A formula calculates a tax bracket based upon taxable income and one of four filing statuses; I would like to be able to either manually enter an amount of taxable income or have a number that is calculated elsewhere inserted in the cell; I created a trigger to insert the calculated cell by using an "IF" statement, but how would I complete the formula to allow for a manual insertion A1 is the trigger - If "Yes", then the calculated number that is contained in cell A2 is entered into B1 (which is the blank cell used for inserting taxable income) [Cell B1 ...

access report 03-29-10
i have a combo box in a form to select 1 of 5 values and i setup list so each selection has a number value ie, 1. table 2.dessert etc. i have check boxes on the report that have to be checked based on the value selected on the form. i have to show all check boxes on the report but only check the box for the value choose in fhe form. i have tried to do conditional statement on the report and check for numeric value and then set check value to true based on that but haven't had any luck. any help greatly appreciated. Submitted via EggHeadCafe - Software Developer Portal of Choice ...

Keeping Formulas Not Data
I input my formula, but would like to fill in the data later. How do I save the Formula without there being data. I am trying to create time sheet that will be filled out weekly so the data will change. I want the formulas to add hours etc. to be in the worksheet with or without the data. Help!! You don't need data in order to have formulas. What is the real question here? Do you want the cells with formulas to look blank until data is entered? I suggest you trap for no data. In B1 enter =3DIF(A1=3D"","",A1) will return a blank cell until = d...

WDM to WinCE porting 02-06-10
Hi I am porting a WDM driver to WinCE. I changed the following WDM Code // Create a WDM Memory Descriptor List for this chunk of memory m_pMDL = IoAllocateMdl ( a_pMemory, (ULONG) a_ByteCount, FALSE, TRUE, NULL ); // Make sure the user's memory block is entirely valid, // then lock all the pages in physical memory. MmProbeAndLockPages ( m_pMDL, UserMode, IoModifyAccess ); // Get the kernel-mapped address of the buffer m_pLockedKernelBuffer = MmGetSystemAddressForMdlSaf...

formula problem #16
Hi I have the following formula that excludes break times from an overall running time Column B contains the start time of a stopage Column c contains the end time of a stopage column k holds the start times of breaks column L holds the end time of breaks column B column C column K column L 9:00:00 9:20:00 09:00:00 09:20:00 12:00:00 12:30:00 12:00:00 12:30:00 14:00:00 14:17:00 14:00:00 14:17:00 =60*24*((C2-B2)-IF(AND(C2>=$K$2,C2<=$L$2),C2-$K$2,0)-...

Converting formulas to value
I have a workbook in Excel 2007 that contains cells which are external references of another source. This is a monthly workbook that I now wish to save and transfer to a disk for storage. I believe that I will lose the amounts shown if the formulas are not converted to value first. If this is correct how can I convert the formulas to value? Thanks very much for your help. Gene L. "Gene L." <elieve@tampabay.rr.com> wrote in message news:ebpcaMl2IHA.3920@TK2MSFTNGP02.phx.gbl... >I have a workbook in Excel 2007 that contains cells which are external >references of a...

Formula for tracking Nassau golf bets
I'm trying to track a golfing Nassau bets between two teams. In columns B & C are the two teams respective scores on each hole. Column D tracks how Team 1 stands for Bet #1. If Team 1 has the highest score on hole 1, they are -1, if they are lowest on hole 1 they are +1, and if they tie, they are 0 (zero). Each hole then increments column D by one, up or down, or remains the same depending on the outcome of that hole. Once a team is up or down by 2 a new bet (Bet 2) starts and is tracked in column E. (Etc. to Bets 3, 4 and perhaps 5.) Bet two, once started should increment ju...