Tag: if else

Top 5 Things to Know in Excel

I remember listing “Microsoft Excel” as a skill on my resume just out of college. The only functions I knew how to use were SUM() & Concatenate(). I also knew that to start an equation, I needed to put an equals sign at the beginning of the cell. “Why would I need to know anything else?” I thought.

Since then, I have slowly expanded my knowledge of Excel and present to you what I now believe are the top five things to know in Excel if you want to list it as a “skill” on your resume:

1. Pivot Tables

If there were only one feature of Excel you could learn in the 15 minutes before a job interview, it would be Pivot Tables. This allows you to summarize hundreds or thousands of lines of data in a few moments. Add, Subtract, Divide, Count, & Parse data with this feature and impress the higher ups.

Articles to Check Out:

2. INDEX-MATCH and/or VLOOKUP

VLOOKUP

VLOOKUP is a function you can use to lookup data from a table. Think of sales data. Imagine you had a table listing the dollar amount of sales by the date. VLOOKUP would allow you to lookup the sales $ on a specific day. There are certain limitations to this function, very specific ways the data must be structured, and quite frankly a little confusing given that there are usually easier ways to find the same information.

Check out: VLOOKUP Article

INDEX & MATCH

In my opinion these are the formulas you should learn before learning VLOOKUP. With these two functions together, you can not only lookup data from a data set, but you can match two sets of data together for QA purposes, ensure data is consistent across multiple reports, and much more.

Articles to Check Out:

3. Informational Functions & Boolean Logic

Boolean Logic can be distilled down into two words: TRUE & FALSE. Boolean Logic is a form of logic where you’re testing whether or not an argument is TRUE or FALSE. Based on the outcome of that test, you can then test for other criteria, write a certain Text string, or perform a function.

Articles to Check Out:

Once you’ve learned to evaluate whether or not something is TRUE or FALSE, you can follow that up by wrapping the result in an IF() statement, i.e. IF this statement is TRUE, do THIS. IF not, do THAT.

4. Basic Text Formulas

There are a large group of TEXT() formulas that are built specifically to deal with, you guessed it, TEXT. These functions are very useful for pulling out only the part of the string you most need from a cell.

The most commonly used formulas (based on my experience) are found below:

5. How to use the IF formula

The IF function is vital in the Excel Ninja’s repertoire. It allows you to easily add conditions to your formulas. If the formula is TRUE, do this. IF it’s FALSE, do that.

NESTED IF statements are when a user stacks multiple IF statements on top of each other. Imagine you had a statement that could be TRUE or FALSE, and you built an IF statement around that. Then imagine that if that statement evaluates to TRUE, there would be another two situations that could occur. You would have to “nest” your IF statement within the first IF statement.

 

Using IFERROR to trap and handle errors

When I first found out about IFERROR, I was a little confused. “There’s a formula to use if we generate an error? Isn’t the point of Excel to avoid errors altogether?” I quickly found out how naive I was.

Whether you’re conducting a MATCH test to see if your data exists in another table or just dividing two numbers, you can get errors. When you’re working with hundreds or thousands of lines of data, its not always practical to go through one by one and see what types of errors your formulas are generating. You may just want to know if there is an error. If that’s the case, errors might be totally acceptable using the formula you wrote, but you don’t want to deliver an Excel spreadsheet with a large number of cells containing “#N/A” or “#DIV/0!”.

Example 1: Dividing by Zero

iferror

In this example, we’re diving column A by column B to produce column C. Further down column C, we have some “#DIV/0!” errors, which just means we divided by zero. In this case, we can use IFERROR to change the result of those errors.

iferror-2

In this example, we’ve simply programmed the formula to spit out the word “ERROR” if one is found. At the very least, your resulting data looks cleaner. Depending on the case, you may have to check your data in Column B because there should be no errors. However, if it is acceptable for column B to have a value of zero, your formula in column C now produces a much cleaner result.

The IF Function in Excel & Boolean Logic

If new to Excel and building formulas, there’s a concept I think is very important to understand before learning to build formulas using IF, SUMIF, COUNTIF, etc.. It’s called Boolean Logic.

Don’t let the name throw you! Underneath the hood of this concept is the simple reality that all you’re doing is building statements that equate to either TRUE or FALSE. 1 or 0. Yes or No. Based on which outcome you arrive at, your equation will do option 1 (TRUE) or option 2 (FALSE). Option 1 or Option 2 is what we’re trying to calculate, but we have to first figure out whether the statement is TRUE or FALSE.

IF() FORMULA

Format: IF(TEST, Value if True, Value if False)

Example:

If+statments+excel

Above we have the Q4 2016 data for every sales employee in the company. The CEO has decided that if an employee has had over 40 sales in one month, he/she gets a bonus. Let’s build an IF statement that determines which employees are eligible.

IF+statement+excel

The first thing to check before crafting your IF statement is the Boolean Logic driving your formula. We’ve created a new column that checks if the value in column D is greater than 40. If TRUE, the formula returns “TRUE”. Otherwise, it returns “FALSE.”

Now that we see our formula is working, we can craft an IF statement that will return specific values that depend on the Boolean value of the original statement.

IF+then+Excel

Let’s look back to the format of the IF() function:

IF(TEST, Value if True, Value if False)

In the example above, we’re testing whether or not the number of sales is greater than 40. If it is, we return the string “BONUS”. If the number of sales is less than or equal to 40, we return the value “Work Harder.”