[mso] Re: IF formulas in Excel

Thank you so much, Hutch.   The formula works perfectly.  
I did find out from our IT group that we are in fact using Excel 2002 and I was 
told that if I bought the new version and didn't buy it for everyone else some 
templates I created for them in 2007 wouldn't work in 2002.  This is 
unrealistic as I serve as the QA Coordinator for a department of over 600 
people and I certainly don't get paid enough to buy 500 copies of Excel 2007.
I think from this 1 email discussion it is painfully obvious that I should take 
a class in Excel.  The 3, 1 hour sessions that were offered through our 
learning at work group I could have taught since I know how to copy and paste 
and write many basic formulas, formatting, charting, etc.  Does anyone have a 
suggestion about how I can learn how to write more complex formulas in Excel?  
Books, class, training course, etc.....?  I know Excel has much more capability 
than most people give it credit for.

I would like to ask 1 more question to finish off the sheet I'm working on but 
I would like just a hint so I might try to navigate making the formula for 
myself.  Plus will give me something to do over the weekend besides clean the 
cat litter and do laundry.

In the final column of data (Column F) for the Molecular Diagnostics Lab is 
turn around time of each patient specimen they run.  Since all canceled tests 
have a TAT of 0.00 days, I don't want them included in the average.  So my 
basic formula would say =AVERAGE if b:b is BCR (or whatever test code) and f>0.0
My real question is: Am I on the right track using AVERAGE or is there 
something called AVERAGEPRODUCT or am I not using the right beginning?  

Thanks,

John
>>> Thomas Hutchins <hutch99999@xxxxxxxxx> 3-27-09 14:44 >>>
You can do that easily with SUMPRODUCT:
 
=SUMPRODUCT(--(B1:B10000="BCR"),--(C1:C10000="C-DU"))
 
Please note that, unless you are using Excel 2007, you can't refer to whole 
columns using SUMPRODUCT.
Hope this helps,
 
Hutch

--- On Fri, 3/27/09, John Perrin <jperrin@xxxxxxxxxxxxx> wrote:


From: John Perrin <jperrin@xxxxxxxxxxxxx>
Subject: [mso] IF formulas in Excel
To: mso@xxxxxxxxxxxxx 
Date: Friday, March 27, 2009, 1:17 PM


-----Inline Attachment Follows-----


After reading some of the posts in your archive section I think this might seem 
like an elementary question to college students but I'll ask it anyway because 
a professor used to tell me the only truly stupid question is one that you need 
an answer to but chose not to ask.  Or at least something along those lines.
I would like to create a template for the labs that I oversee for data 
collection and management where the supervisor could put the template on their 
desktop, dump the required data into the appropriate columns and the formulas 
off to the right would give them the needed numbers for that month.
I have all of the COUNTIF formulas working fine so I can see volume of testing. 
 What I can't seem to tease out of a formula is a calculation that looks at 
column B for a specific test, then at column C for a specific result and count 
only those that meet both criteria I have set.  I thought I had the formula 
down today but doesn't quite seem to work.  Just a little background (BCR is 
test code in our Molecular Lab for genes involving Breast Cancer and C-DU is a 
test canceled because it is a duplicate specimen).  Here is the formula I have 
so far:
=(COUNT(IF,B:B="BCR",1,0)*AND(COUNT(IF(C:C="C-DU",1,0)))

Column B contains the test code for each test run and column C has all of the 
test results or whether the test was canceled.  In column C there also results 
of "POS", "NEG" or "INC" for inconclusive.  Each time I change the "C-DU" to 
"POS" or "NEG" the number from the formula is always 3.  Just to make sure I 
have verified all of the data in the worksheet.  In February 2009, our lab 
received 91 specimens for BCR testing, 40 were NEG, 6 were INC, 2 C-DU, 1 C-OE 
(order error) and 42 were POS.

John Perrin
jperrin@xxxxxxxxxxxxx 

**********************************************************
Electronic Mail is not secure, may not be read every day, and should not be 
used for urgent or sensitive issues
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx 

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso 

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm 
*************************************************************



      
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx 

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso 

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm 
*************************************************************

**********************************************************
Electronic Mail is not secure, may not be read every day, and should not be 
used for urgent or sensitive issues
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group. 
 This group will not allow for posting of emails, but will allow you to join 
and share problem files, templates, etc.:  
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE 
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: