By popular demand, we’ve decided to put together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  This list of 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  Thus, we’re starting with something that might qualify as relatively basic (IF statements), but rest assured that IF statements can get pretty complex pretty quickly when nested. For each of the topics in this list, we’ll refer you to some videos that we’ve pulled together to comprehensively explain the technique, formula or concept. IF statements (including standard IF statements, nest IF statements, COUNTIFs and SUMIFs) are covered in a series of 4 videos on IF statements that you can find here. While the full series is available for purchase, individual videos (easily accessible through the previews embedded below) are also available for purchase.

IF statements are basically formulas in Excel (related to IIF formulas in Access) that are structure to return one of two things depending upon whether a provided criteria is met or not.  There are three different parts to an if statement: IF(decision criteria, value if true, value if false).  Thus, let’s think of an if statement that is designed to return “>50″ if a cell value of A2 is greater than 50 and “<=50″ if it is not.  An if statement to accomplish this would have the following parts:

*  Decision Criteria: A2>50
*  Value if True: “>50″
*  Value if False: “<=50)
Putting these elements together, we end up with:  IF(A2>50, “>50″, “<=50″).

If you’d like additional help with IF statements, you should definitely check out our video tutorial on them:

So, this is how we’d use an IF formula to get Excel to assign one of two different values to a cell depending upon whether a specified criteria was met or not…. But, things can get much more complex than that.  IF statements can be “nested” (“nested If statements”) in such a way where multiple IF statements are used in one formula in order to return one of several different outputs depending upon whether any of multiple different decision criteria are met.  Nested IF statements can get pretty tricky, but there are definitely ways to approach them in order to make them easier to formulate and understand.

We’ve actually created two video tutorials on nested IF statements that will walk you through some different approaches and some complex examples:

The final types of IF statements that you should be familiar with (other than standard IF statements and nested IF statements), are COUNTIF and SUMIF formulas. These formulas are for calculating conditional sums (sum of numbers that meet a specified criteria) and conditional counts (count of numbers that meet a particular criteria). SUMIF, in particular, can be applied in such a way that the sum is calculated on a different field or array than the decision criteria is looking at. We also pulled together a video on SUMIF and COUNTIF formulae:

Other types of IF statements that you’d like tutorials on? Let us know, but be sure to check out the Microsoft Excel IF statement video series to get up and running!