Microsoft Excel 2010 training on advanced formulasHow to build the IF function in Microsoft Excel 2010  
 Home Products Programs Meet Dawn Free E-zine Media Room Contact 
Excel: IF Function  

Technology Trainer
Software & Tech Speaker
Software Consultant
Tips & Tricks
The Pro Shop
Free Software Articles
How-To Lessons
Tips Newsletter
Bonus Resources

 
Dawn Bjork Buzbee
The Software Pro

Microsoft Certified Trainer (MCT)

Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor

Certified Microsoft Office 2007 Specialist (MOS 2007) Master Instructor

Microsoft Certified Application Specialist (MCAS) Instructor

Certified Microsoft Office Expert

Certified Women's Business Enterprise (WBE)
WBENC Certified

WOSB (Women-Owned Small Business) Certified

(303) 699-6868
Dawn@TheSoftwarePro.com

>>Back to Articles Home

Microsoft Excel:
Easy Data Analysis with the IF Function

excel IF function, analyze data, functions, microsoft excel 2010 formulas, tips and tricksAlthough SUM is the most frequently used function in Excel, the very useful IF function should also be in your Excel bag of workbook tricks. Basically, the IF function tests whether a condition is true or false and then performs an action such as a calculation or data entry. How often are you sorting or filtering data to manually locate Excel entries that might require additional data entry or auditing? The IF function can automatically evaluate your data based on the condition or conditions that you create.

Conditions Can Be Formulas, Values, or Text

The cell or entry that is evaluated may be a formula, value, or text; the displayed result may also be a formula, value, or text answer. For example, if an amount is more than 5% over the budgeted amount, then you might display "OVER", otherwise show "OK."

First, let's look at the structure (syntax) of the IF function. As with other Excel functions, we start with =(equals) and then the function name followed by an open parentheses, such as =IF( . The logic and structure of an IF function is:

excel IF function, analyze data, functions, excel 2010, excel 2007, excel 2003, tips and tricks

=IF(what you are testing/evaluating, what to do if true, what to do if false)

For instance, what if we want to calculate an analysis where, if the total is greater than or equal to $1000, then a $100 bonus is entered into the formula cell; otherwise, no bonus is given.

excel IF function, analyze data, functions, excel 2010, excel 2007, excel 2003, tips and tricks

The formula would look like: =IF(B2>=1000, 100, 0) where B2 is the value being evaluated. As with other formulas, the results will update if values are changed. And, as with other formulas, this calculation can be copied down or across to calculate additional values.

The results of the IF function can also be a text entry that you may want to filter for checking data, such as =IF(B14=E14, "OK", "AUDIT NEEDED").

In this sample function, if the two cell values are the same, the result is "OK", otherwise the answer is "AUDIT NEEDED". Notice that text entries are surrounded by quotes (to create a character string) with the formula commas outside the quotes. Note that it doesn't matter if you add spaces after each comma.

But Wait...There's More! Nested Functions

Not every evaluation has only one condition limited to two different actions. Sometimes, you may have 3 or more possibilities, for instance, different calculations based on ranges or levels of values. This calls for nested/multiple IF functions. You can also nest other functions within an IF function as needed to create your logical condition. For instance, an IF function might apply a condition to the results of a SUM or AVERAGE function.

For example, the following IF function works with these options:

  • If the value is less than $25,000, multiply by 10%
  • If the value is less than $50,000 but at least $25,000, multiply by 20%
  • Otherwise (the value is greater than $50,000), multiply by 30%

The formula would look like:

excel IF function, analyze data, functions, excel 2010, excel 2007, excel 2003, tips and tricks

NOTE: When nesting with any worksheet function, the equals sign is only required with the initial function statement, i.e. the 2nd IF in our nested formula does not need an equals sign. You can use up to 64 levels (in Excel 2010 and Excel 2007 only; 7 levels in Excel 2003) of nesting in a formula but that's a lot of nesting!

Complex Functions Don't Have to be Complicated

Ready for some more function fun? The IF function can be combined with AND, OR, NOT functions to create more detailed evaluations that apply multiple conditions where all expressions are true (AND), only one expression needs to be true (OR), or the opposite (NOT) is true.

Looking at the breakout of Excel functions is an easy way to learn how to create your own especially with nested functions. This next example, shown below, is designed to test:

excel IF function, analyze data, functions, excel 2010, excel 2007, excel 2003, tips and tricks

  • If cell B2 is between 750 and less than 1000 (AND), enter 75 in cell
  • If cell B2 is greater than or equal to 1000, enter 100 in cell
  • Otherwise, enter 0 in cell (the not true/false portion)

Grab your own detailed reference on how you can create your own IF functions in Excel:
http://www.TheSoftwarePro.com/handouts#excel.

Dawn Bjork Buzbee, MCT, The Software Pro

Dawn Bjork Buzbee is The Software Pro and a Microsoft Certified Trainer (MCT) as well as a certified Microsoft Office Specialist (MOS) Master Instructor, certified Microsoft Applications Specialist (MCAS) Instructor, and a certified Microsoft Office expert. Dawn shares smart and easy ways to effectively use software through her work as a software speaker, trainer, consultant, and author of 8 books.

This article and more can be reprinted at no charge in your publications and website with copyright and attribution.
>>Learn more about how easy it is to share these valuable tips, tricks, and techniques.

More Microsoft Excel Tips, Tricks & Handouts Too!

More Smart & Easy Software Tips, Tricks & Techniques for You

<<Back to Articles
 


Be an Excel Expert!
How effectively are you using Excel to create, format, and analyze your important data? Become an Excel expert with the
Excel with Excel Learning Series. Master functions, filtering, linking, PivotTables, charts and graphics, macros, and more!
Click Here to Choose Your Excel Training

 

Go To Top of Page
 



Home for Dawn Bjork Buzbee, The Software Pro
Contact Dawn Bjork Buzbee, The Software Pro
Hot Excel Tip
Press [F2] to edit any Excel formula.
 
Did You Know?
Excel 2010 & Excel 2007 worksheets now have 1,048,576 rows and 16,384 columns.
 
 
 
www.TheSoftwarePro.com
HomeContact Us | Training | Speaking | Consulting | Services | Tips & Tricks | Products | How-To Lessons | Free Articles | Newsletter | Resources | Site Map

Copyright 2015 The Software Pro & Dawn Bjork Buzbee - All Rights Reserved
(303) 699-6868
Dawn@TheSoftwarePro.com www.TheSoftwarePro.com
Geek Speak Becomes People Friendly

Microsoft Certified Trainer (MCT)
Certified Microsoft Office 2013 Specialist (MOS 2013) Master
Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor
Microsoft Certified Application Specialist (MCAS) Instructor
Certified Microsoft Office Specialist (MOS) Master Instructor
Certified Microsoft Office Expert
Certified Women's Business Enterprise (WBE) WBENC Certified
WOSB (Women-Owned Small Business) Certified
The Software Pro
is a registered trademark
Terms & Conditions       Privacy Policy