[mso] Re: IF formulas in Excel

There's no reason you can't use Excel 2007 to develop spreadsheets for Excel 
2002 users if you keep in mind the differences between the two versions and 
don't include functions, references, etc. that are unavailable in the older 
version. Naturally, you will need to Save As the older format.
 
As for your AVERAGE question, you can do it all with SUMPRODUCT, or with an 
AVERAGE(IF( array formula. An array formula must be entered by pressing 
CTRL+Shift+Enter and not just Enter. If you do it correctly then Excel will put 
curly brackets around the formula {}. You can't type these yourself. If you 
edit the formula
you must enter it again with CTRL+Shift+Enter. 
 
Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
If we adapt our earlier SUMPRODUCT formula, we can count the cells to be 
included in the average:
SUMPRODUCT(--(B1:B10000="BCR"),--(F1:F10000>0))
 
We can also make it sum the BCR TAT days:
SUMPRODUCT(--(B1:B10000="BCR,F1:F10000)
 
We can also get the sum of all the BCR TAT days using SUMIF:
SUMIF(B1:B30,"BCR",F1:F30)
 
The last two formulas include the zero days in the sum (doesn't change the 
total).
Dividing the sum by the count gives the average.
 
To create an AVERAGE array formula with two conditions, multiply the two 
conditions within an IF function. That's the hint you requested. Let me know if 
you want the actual formula.
 
A Google search can find help for almost any Excel question you can ask. There 
are also lots of great web sites with Excel tips and tutorials. Here a few of 
them:
 
www.contextures.com
www.cpearson.com
www.rondebruin.nl
www.xldynamic.com
www.contextures.com
www.dicks-blog.com
www.mcgimpsey.com
www.ozgrid.com
www.mrexcel.com
 
Hope this helps,
 
Hutch
--- On Fri, 3/27/09, John Perrin <jperrin@xxxxxxxxxxxxx> wrote:


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


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


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
*************************************************************



      
*************************************************************
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: