[Training Video] Learn How to Use IF Statements

Use IF Statements to Boost Your Data Analysis Mojo

Are you confident that you have the tools and expertise needed to harness the powerful data your company is collecting? 

Becoming a ‘data-driven organisation’ sounds like a trendy marketing term. Still, it’s difficult to discount the valuable insight that can be gained by a deep dive into your business data. While the era of Big Data language has passed, data is more significant than ever before — with companies capturing terabytes of information about their customers, their products, their competitors, and the world. However, utilizing this data is more challenging than ever — partly due to the proliferation of information that can easily cause business professionals to become paralyzed with data overload. How can you parse through the digital reams of information and find the nuggets of data that will help you make significant business decisions, improve revenue, and simplify operations? There is a powerful tool that many people don’t realize is already in your hands: Microsoft Excel’s IF statements.

What Are Microsoft Excel IF Statements?

According to Microsoft’s support site, an IF statement is one of the most popular functions in Excel and “allows you to make logical comparisons between a value and what you expect.” This simple function (or formula) is incredibly powerful in the world of data analysis, providing business users with a way to quickly interpret trends in even the largest data sets. Excel’s IF statements are often used together with THEN, allowing users to define IF action A happens, THEN action B should follow. In practice, IF statements will enable you to calculate sales tax based on whether an item is taxable or not, quickly identify items that are over or under a set budget amount, and more. The IF statement includes two main arguments: the result if your comparison is True and the result if your comparison is False.

How to Use IF Statements in Data Analysis

The formula to use an IF statement in Excel is:

=IF(logic_test, value_if_true, value_if_false)

Used in nearly all programming languages, this simple statement can be used in a variety of ways. A simple scenario would be determining which staff members meet the minimum qualifications for a sales-based bonus. Assume that individuals receive a bonus if their sales meet or exceed $250,000 for the time period.

The steps to complete this action include:

1. Create a list of staff member names in Column A in Excel, starting at Row 1
2. In Column B, list the sales volume for each staff member next to their name
3. In Column C Row 1, you will place this formula: =IF(A1>=250000, “Yes”, “No”) [Note: “>=” means “greater than or equal to” in Excel terms]
4. Copy the formula in C1 down until you have a calculation next to each of the employee names
5. The result of Column C will tell you whether that individual is eligible to receive a bonus!

While this is a straightforward formula, it’s easy to see how you could leverage this functionality for much more advanced data analysis for items such as past-due notices, pass-fail grading scenarios, and more. You can even determine sliding scale bonus options with a nested IF statement, allowing you to make multiple comparisons and decisions in a single calculation.

In the spreadsheet you already created, let’s assume that $250,000 in sales nets a 10% bonus, while $500,000 in sales gives a 12% bonus — and use a simple nested IF statement to identify the bonus structures and payout quickly.

1. In C1, enter this formula: =IF(A1<250000, “No Commission”, IF(A1>=250000, A1*10%, IF(A1>=500,000,A1*12%)))
2. Copy this formula down until the calculation is next to each employee’s name

That’s all! Instead of spending several minutes on each staff member to manually quantify their bonus, you can automatically calculate that information using IF statements. Then, you can use the SUM function in Excel to capture the total amount you will be paying in bonuses for the time period.

Whether you have full-time staff that are interested in learning more about data analysis or simply want to see how far you can take your knowledge, understanding the power of “IF” statements and other tools that are hidden within Microsoft Excel can have a dramatic impact on your business. The IT consultants at Sydney Technology Solutions is available to help with your toughest technology challenges from Big Data structures and analytics to cloud-based storage solutions for your business applications and information. Contact our team at (02) 8212 4722 or chat online with our friendly professionals to schedule your complimentary initial consultation.