Excel 2016: IFS Function

The following post was created by Kasper Langmann as a guest post for Excel Exposure.  If you’d like to see more of his work, check out Spreadsheeto for more Excel tutorials.

Follow along with the lesson via this workbook that he created specifically for this article.

How To Use the IFS Function

With the release of Excel 2016, Microsoft included several new functions. One of those new functions is the ‘IFS’ function. The ‘IFS’ function builds upon the ‘IF’ function in such a way that it simplifies one of the most common uses of the ‘IF’ function.

In order to have a discussion about the ‘IFS’ function, it would be a good idea to review the basics of the ‘IF’ function. Both functions are logical functions which means that they return a value based on whether some logical test is TRUE or FALSE. For instance, we might want to know if a number is less than 10. In this case, the ‘IF’ function is designed such that if a given number is less than 10, its output is a TRUE result. If a given number is not less than 10, then the output of the ‘IF’ function is a FALSE result.

Excel also allows us to combine multiple ‘IF’ functions into the same formula for a more complex needs. This is the feature that ‘IFS’ improves upon. But more on all that in a bit.

The basics of the ‘IF’ function

Now that we have a bit of background about the ‘IF’ function, let’s look at the syntax:

‘=IF(logical_test,[value_if_true],[value_if_false])’

The items inside the parentheses are what we refer to as arguments. As you can see, the ‘IF’ function has three arguments. The first argument is fundamental to the function. It is the logical test by which we are seeking a TRUE or FALSE result.

In our previous example, our ‘logical_test’ is ‘5 < 10’ if the number we were testing was ‘5’. For our next two arguments, we can choose what we would like for the function to return in the case that the number we are testing is actually less than 10. In this case, we will keep things simple and actually use “TRUE” and “FALSE”as results (we could have easily chosen “Yes” and “No”, or “1” and “0”, etc.) .

So now let’s take a look at this in an actual worksheet:

picture-1

This is the example we just discussed and as you can see in the formula bar, we have chosen the text strings “TRUE” and “FALSE” for our ‘value_if_true’ and ‘value_if_false’ arguments, respectively. Note that if we use a string for these arguments, they must be enclosed in double quotes.

The IF function can be combined with several other functions to increase its functionality. See this video on how to use the functions: IF, IFERROR, OR, AND, TRUE, FALSE and NOT.

Now let’s take a quick look at an example of two ‘IF’ functions in the same formula, often referred to as nested ‘IF’ statements:

picture-2

Take special notice that the second ‘IF’ function actually serves as the ‘value_if_false’ argument of the first ‘IF’ function. Based on our previous example, it should be clear that if the given number we are testing is less than 10, this nested ‘IF’ formula would output ‘less than 10’ and be done. However, in this case, where 11 is the number being tested, the formula is set up so that the false result for the first ‘IF’ test is now the second ‘IF’ test and it will now result in a TRUE result, or ‘less than 20’.

The nested ‘IF’ idea can be expanded up to seven ‘IF’ functions. While that can be powerful functionally, it’s not difficult to imagine how that could become unmanageable at some point from a testing and troubleshooting perspective.

Experts have devised all kinds of workarounds to the nested ‘IF’ function for many years due to its limitations. However, with the release of Excel 2016, Microsoft has now come up with a better solution: the ‘IFS’ function.

How to use the ‘IFS’ function in excel

The ‘IFS’ function is Microsoft’s answer to the nested ‘IF’ function. It allows for the same functionality in a simpler form designed for multiple conditional tests.

The ‘IFS’ function essentially tests multiple conditions until the first true is returned. This may make more sense by looking at the syntax:

‘=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, …)’

So let’s see how this works with the previous scenario:

picture-3

Note the contrast of the formula here compared to the previous example using a nested ‘IF’ formula. For starters, the ‘IFS’ function is a far more straightforward method with improved readability. It is a very intuitive method of achieving the same results.

One thing that might not be obvious at first with the ‘IFS’ function is that its lack of an argument for a FALSE result to any of the logical tests needs to be considered. For instance, in our example, if we were to instead choose the number 21 as our test value, we will get an error if the formula is otherwise left unchanged:

picture-4

So in order to address this, we simply add a last logical test that is always TRUE (like ‘1 = 1’) and select the value we want the formula to return in the event that all previous logical tests turn out to be FALSE.

picture-5

A practical example

Now that we have covered the basics of how to use the ‘IFS’ function, let’s apply it to a real-world example. Let’s consider a situation in which we have a measurement for hand size and we want to convert that to glove sizes. The following table shows our conversion from the measurement to the size.

picture-6 

So the concept here is that the recommended size for hand measurements 6.5 or less is ‘XXS’, 7 or less is ‘XS’, 7.5 or less is ‘S’, and so on. These statements are our logical tests and resulting values if TRUE. And if we have a short list of measurements, we can use the cell references in our arguments to match up to sizes based on the measurement conditions:

picture-7

Note the similarity to our more basic example earlier. This is a great illustration of the ‘IFS’ function at work in a real life type of scenario. We can also replace the literal values in our formula with the cell references in our cross reference table (found two pictures above in the range of A2 to D2):

picture-8

The ‘IFS’ function is just one of the many new features available in Excel 2016 and we have just shown how useful it can be. It builds on one of the most widely used logical functions providing an even broader range of flexibility. With its simpler approach to tackling the needs only nested ‘IF’ functions could previously address, the ‘IFS’ function figures to become a mainstay for even the most inexperienced Excel user. Read more on the use of the IF and IFS functions here.

This guide is written by certified Microsoft Office Specialist Kasper Langmann from Spreadsheeto. Did you like this guide? Then go check out his free Excel training.

What-If Analysis

Another helpful Excel guide from Acuity Training’s Nick Williams.

What-if analysis is a useful way of being able to test out various scenarios in Excel.  You can look at these things two different ways.

The first way is to change the input variables and see what impact that has on the output. The scenario manager and data tables work in this way and can be used to answer questions like, what would happen to our profits if the number of units we sell doubled, or what would happen to our profits if the cost price of each of our units sold increased by 10%.

The second way is to say what outcome you would like to have and ask Excel to calculate what change in the inputs would be required to achieve this. The goal seek feature works this way and can answer questions such as how many units of a product need to be sold in order to reach a desired profit level.

Scenario Manager

The following example looks at scenarios showing the profit from selling 100 apples, with differing levels of mark-up.

The first step is to create some scenarios.  The scenario manager can be found on the Data ribbon, under What-If Analysis.  Click the Add button to start creating new scenarios.  The next dialog box will ask for a name for the scenario and which cells are to be changed.  A scenario can contain up to 32 changing cells, although in reality, most scenarios will use far fewer than this.  Once the scenario name and cells to be changed have been selected, click the OK button and fill in the values for each cell to be used in the scenario.  From here, click Add to continue adding more scenarios, or OK when finished.

For this example, four scenarios have been created – for a 50%, 100%, 150% and 200% markup.  There are two ways that a scenario can be applied – it can either be shown on the worksheet itself, or a summary can be created.  The below table shows the results when a 50% markup is applied.  This was achieved simply by selecting the 50% Markup scenario from the Scenario Manager and clicking the Show button.  50% has been entered into the Markup column, and the figure in the Profit column, which is calculated using a formula referring to this has updated accordingly.

what-if-image-01

To compare results from several scenarios simultaneously, the Summary option can be used.  As with the Show option, the Summary report is accessed from the Scenario Manager dialog box.  There are two options here; Scenario summary and Scenario PivotTable Report.  Although the option is there to display the results as a PivotTable report, in most cases, the summary will display the results in a more user-friendly way.

The below shows the results from the scenario summary, demonstrating that the profit changes depending on the markup scenario used.  Note that the Changing Cells and Result Cells are shown using their cell references.  In this example, they have been left so that they can be referred against the table above, however in practice, it will make the report easier to read if the cells have been named.

what-if-image-02

Goal Seek

The scenario manager is good for known variables, however, sometimes it is desirable to work backwards.  Using the same example of selling 100 apples, we might want to know what markup we would need to use in order to achieve a profit of £25.  Goal Seek can be used to answer exactly these sorts of questions.

Goal Seek can be found on the Data ribbon, under What-If Analysis.  It simply asks for three parameters.  Set cell refers to the cell we want to contain the goal value.  In this case it is E2, the cell showing the profit on apples.  The value in the To value box should represent the goal, in this case 25, representing the desired £25 profit.  Finally, the By changing cell box should show the cell reference for the cell to be manipulated in order to achieve the goal, in this case the markup.

what-if-image-03

Upon pressing OK, Excel will look for a solution, displaying a dialog box like the one below when it has finished.  Note that in the example below, cell C2 has been updated to show a 250% markup and cell E2 showing the profit has updated accordingly.  Pressing OK will confirm these changes and commit them to the worksheet, while pressing cancel will see the cells revert back to their previous values.

what-if-image-04

Data Table

Scenario summaries give a table showing data from various scenarios, however, they do not update if the data they are based upon changes.  In the apple sales example, we might not need scenarios based on the cost price, as this is a non-controllable factor, yet it could still change in the future.  The scenario summaries based upon it would not change if this was updated, whereas a data table would.

Data tables can be based on either one or two variables.  For a single variable, a two column data table is required.  The first column should contain the variable, whilst the second should be left blank for Excel to populate.  The exception to this is the very first row, where the second column should show the formula on which the calculation is based.

what-if-image-05

To populate the data table, select the entire table (in the case of this example this would be cells H2 to I6) and navigate to What-If Analysis on the Data ribbon, and choose Data Table from the drop down menu.  On the dialog box that appears, select the cell containing the variable as the column input cell.  This tells Excel to use the value in the input column instead of this cell in the formula performing the calculation.  Press OK, and Excel will populate the rest of the data table.

what-if-image-06

A two variable data table works in much the same way, however the layout is slightly different.  Instead of consisting of two columns, there should be one column containing the values for the first variable, with a row containing the values for the second variable.  The formula should go in the top left corner, where the two meet.

what-if-image-07

As with a single variable data table, highlight the entire table, so B6 to F10 in the example, and select Data Table from the What-If Analysis Dropdown on the Data ribbon.  This time, enter the cells containing both the row and column variables in the formula.  Press OK, and Excel will populate the table.

what-if-image-08

Infographic – Benefits of Learning Excel

Why should you learn Excel?

SkilledUp.com was nice enough to share this infographic (below) with Excel Exposure.  It details some good reasons for why Excel can be a powerful tool to learn to use.

If you’re on the fence about learning Excel, take a look and see how it can benefit you.

Feel free to leave comments below with anything you think they’ve missed.

career value of learning excel infographic skilledup

This infographic was originally published on SkilledUp.com.

VBA Important Tips

Thanks to Nick Williams at Acuity Training for this guest post!

The lesson will go over the following tips:

 

1) Loop Through Code

2) Variable Scope

3) Error Checking

4) Immediate Window

 

VBA Tip #1 – Loop Through Code

A common event in programming is having a section of code which needs to be executed one or more times, this is achieved using a Loop.  There are two types of loops available in VBA:

For … Next Loop – use this when you know how many times the code needs to be executed.

Do … Loop – use this when you need to test how many times the code needs to be executed.

For … Next Loops

When you know the number of times a piece of code needs to be executed, use the For … Next loop, this uses a counter to determine how many times the code has looped at any given time.  The following example shows how a For … Next Loop could be used to enter a value in a set of cells:

Image 1

The value of  i is incremented by 1 each time the Next line of code is executed.

Do Loops

Most occasions in programming we are not sure how many times a piece of code needs to be executed, and in most cases we would test this against a condition, ie. repeat until all records have been formatted, since we cannot determine how many records there may be at any given time, this would be a good example of performing a Do … Loop.  The Do … Loop has several formats it can take on, the following table explains the variations of the loop:

Image 2

The following example shows how a Do … Loop could be used to format a set of cells, the variation is a Do Until and the condition is until the Active Cell does not contain a value:

Image 3

Image 4

VBA Tip #2 – Variable Scope

Variables are constantly used in any programming language to capture and temporarily store values; VBA is not exception to this.  However, the importance of using variables is ensuring you set the scope for the variable correctly.  The Life of a variable is known as its scope:

Image 5

The following code example displays how we would use Procedure, Module and Global scope variables:

Image 6

Image 7

VBA Tip #3 – Error Checking

Once you have tried and tested your programs, the next step is to apply some Error handling which will make as many checks as possible during initialization to ensure that run time errors do not occur later.  If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box.  This may be acceptable, but a more user friendly message may be more desirable. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method.

There are generally three types of Errors:

1.   Compiler errors, such as undeclared variables that prevent your code from compiling

2.   User data entry error such as a user entering a negative value where only a positive number is acceptable

3.   Run Time errors, these occur when VBA cannot correctly execute a program statement.

The more checking you do before the real work of your application begins, the more stable your application will be.

Image 8

Image 9

 

VBA Tip #4 – Immediate WIndow

The Immediate Window within the VBA Editor is used as a scratchpad window where statements, methods and Sub procedures can be evaluated immediately.  Very useful when you want to see the results of a line or two of code, but do not want to run the entire procedure.

Image 10

Image 11

Hope you enjoyed the post, and thanks again to Acuity Training for creating this lesson!

Video Lesson: Error Checking & Data Monitoring

Error Checking and Data Monitoring in Excel

In the following video tutorial, I go over the following items in detail:

Explanation and example of major error types in Excel:

#DIV/0 — #VALUE! — #REF! — #NAME? — #NUM! — #N/A — #NULL!

Overview of Excel’s built-in error checking options and functionality.

How to use “Go To Special…” to find errors easily.

Copy / paste large data areas for data monitoring.

Using direct comparison, and SUM/COUNT functions to compare data changes.

Use of Conditional Formatting for emphasis when comparing ranges.’

Download the Error and Data Checking file to follow along with the video lesson!

Video Lesson: Advanced Pivot Tables – Round 2

Advanced Pivot Tables Round 2

This lesson picks up right where the last Pivot Table lesson left off.

In this tutorial, you will learn:

  • How to use multiple levels of data within row or column labels.
  • Expand / collapse all functionality through right-click (Expand / collapse entire field)
  • Advanced filtering options including: right-click, row label, column label, and report filters. Includes multiple simultaneous filters, and using search/filter functionality within each data level.
  • ‘Defer Layout Update’ option which allows for changing fields without pivot table automatically updating.
  • Fields Section / Areas Section Stacked vs. Other Pivot Field List Formats.

Make sure to download the Advanced Pivot Tables – Round 2 workbook to follow along with the lesson!

If you’re following along with the Lesson Guide, next up is Auto-Filter and Advanced Data Filtering

Video Lesson: Creating and Designing Charts

Excel has very powerful charting capabilities, which many users do not take full advantage of. This lesson goes over a few of the types of charts using some example data.  Here is a quick summary of what is in the lesson:

– How to use the Insert Chart feature for Line, Pie, and Column charts.

– Formatting/editing chart axes, data labels, legends, data series, and chart titles.

– Detailed introduction to Sparklines (which allows for charts within individual cells).

Make sure to download the Chart Lesson workbook to follow along with the video!!

Video Lesson: Beginner’s Introduction to Excel

One video that I have been meaning to do is a lesson focused on those of you who have never (or rarely) used Excel, and needed an introduction to the program and how it works.  I’ve created this lesson in an attempt to introduce the most basic information about what Excel is and go over some examples of what it could be used for.

Disclaimer: If you’re pretty familiar with Excel, this video may be extremely boring.  You have been warned!

Throughout the lesson, I explain the following information and questions in great detail:

– What am I looking at when I open Excel for the first time?

– Explanation of Column / Rows and how referencing works.

– Navigating Excel, knowing where you are in the spreadsheet, and the ‘name box’.

– Overview of sheets including adding, deleting, and renaming.

– Explanation of hard-coded values vs. formulas/functions, and Excel default cell formats.

– Formatting options including bold/underline, alignment (e.g. centering), and creating borders.

– Information about the formula bar, and how to use the ‘Insert Function’ feature.

– Example of creating a simple personal finance tracker and how to input information into Excel.

– Introduction to the Master Workbook, including the function listing and previews of examples from other lessons.

Feel free to show this lesson to anyone who wants to learn Excel but doesn’t know the first thing about getting started!

Now if you’re following the Lesson Guide, you should move on to Learning Basic Formatting Techniques.

Video Lesson: Five Powerful Excel Tips

Make sure to download the most recent Master Workbook to follow along!

I’ve noted the time that each section starts in the video, but you may want to let it load completely first as I’ve had errors skipping around early.

1) Dynamic Named Ranges [0:13]

As a follow-up to the Named Ranges video, I go through some examples which have been partially modified from the Dynamic Named Ranges post on OzGrid.  Definitely check out that link for additional information about how to use the functionality!

Keyboard Shortcuts: New Named Range is ALT + MMD, and the Name Manager is ALT + MN.

To check what your named range is currently referencing, hit F5 (for Go To) and type in the dynamic named range into the reference field, and it will highlight that range.  This is useful since dynamic named ranges do not appear in the Name Box.

2) Find and Select -> Go To Special… [5:28]

An excellent feature in Excel is the Find and Select functionality, which includes the ability to select a certain subset of a range.  For example, you can easily select all cells within a range that contain: Blanks / Formulas / Visible Cells / Constants / Conditional Formats and a whole lot more…

Keyboard Shortcuts: For ‘Go To Special’, hit F5 and then ALT + S.

3) 3D Formulas & Editing – Formulas Across Multiple Sheets [7:32]

Do you have information that is spread across multiple sheets in the same format? For example, you may have the same sheet template with different monthly/weekly/daily information.  Using ‘3D formulas’ you could sum a certain cell or range across multiple sheets by indicating the starting and ending sheets (and that formula will grab everything in between as well).

Keyboard Shortcuts: Selecting multiple tabs is CTRL + SHIFT + Pg Up or Pg Dn.

4) Text to Columns [11:24]

Have a lot of data that’s jammed into a single cell?  You can use Text to Columns to separate that cell’s contents into multiple columns based on the location of spaces, commas, periods or other character(s) that you specify.  An example would be splitting a cell with full names into a first name and last name column based on the space.

Keyboard Shortcuts: ALT + DE brings up the Text to Columns option.

5) Customizing Ribbon Menu Tabs & Menu Groups [13:08]

There is a lot of customization that you can build into Excel so that you have custom menu tabs & groupings to help you get to the Excel features and functionality that you use most.

You may have noticed my ‘Ben’ grouping in previous videos on my ‘Home’ tab which has PivotTable, Name Manager, Freeze Panes, Data Validation, Conditional Formatting and other features that I use very frequently.  In this video I will show you how you can do the same!

Video Lesson: Formula Auditing & Evaluation

Formula Auditing and Formula Evaluation are powerful components in Excel, and today’s video lesson goes over the following uses of the Formula Auditing features:

Show Formulas:  How to change view to show formulas instead of cell results.

Formula Evaluation:  See how Excel views the formula as you walk your way through the calculations.

Trace Precedents / Trace Dependents:  Helps finding out where your data is coming from and/or going to.

Watch Window:  Allows for user to set certain cells into a window that can be viewed as you switched sheets.

Make sure to download the most recent Master Workbook to follow along.

Video Lesson: Advanced Lookup – Index & Match

The versatility and usefulness of the Index and Match functions are cherished by Excel veterans, but many less experienced users have either not encountered them, or are a bit confused by the usage of the two.

This Excel Video Lesson shows you how you can use the Index & Match functions to do advanced lookups which can be more reliable and versatile than just VLOOKUP or HLOOKUP alone.  In the video I go over the following uses of Index & Match:

– Two-Way Lookup (using both a row header and column header to find the intersecting value).  This can be very helpful when columns and rows may be added or deleted to your ranges.

– Averaging entire row/column information based on a lookup value.

– Summing entire row/column information based on a lookup value.

Make sure to download the most recent Master Workbook so that you can follow along at home!

Excel Video Lesson: Naming Ranges and Cells

Named Ranges in Excel

This video tutorial explains how you can name ranges or cells for use within functions & formulas.  It is a great follow-up to the last video lesson on Absolute & Relative cell referencing.

I’ve found it incredibly useful throughout Excel.  It helps to alleviate the concern that you’re pulling information from the wrong area, and makes formulas more easy to follow.

 

If you found this Named Ranges video interesting, you may want to check out the Five Powerful Excel Tips video which goes over Dynamic Named Ranges (which allow for the named ranges to change as more data is added to them).  It’s the first tip in the lesson!

Video Lesson: Absolute & Relative Referencing

Absolute and Relative Cell Referencing

This video tutorial focuses on Absolute vs. Relative cell referencing within Excel.  You likely know it as the dollar sign ($) used in functions/formulas (for example, C5 vs $C$5).

There is also a quick demonstration of the video using a VLOOKUP formula.  The VLOOKUP formula is explained in more detail in these two lessons here and here, but the example in this video gives a basic understanding of how it works in Excel.

Update 5-22: I’ve noticed some users (especially those with Macs) have had difficulty viewing this video.  Since it is one of the older videos, I will be re-recording it and adding information so that it is comparable to more recent videos.

If you’re following the Lesson Guide, next up is Math & Statistical Functions (e.g. SUM, AVERAGE)