Using Excel IFS and SWITCH functions.
Making Rent vs. Buy decisions always seemed like a no-brainer to me. If you hold on to your purchase long enough, you will be saving money: be it a car or a computer software. Case in point: it costs only $ 109.99 to purchase a license for Excel 2016 vs $69.99/year to lease Office 365 Personal, which includes Excel, as well as other Office products. Assuming you only need to use one product that truly matters, and you will use it for more than 19 months, buying is cheaper than renting. The longer you use the product without upgrading, the more money you save. This held true until February of this year, when Microsoft introduced 6 new functions available exclusively to Office 365 subscribers: TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS and MINIFS. This addition alone, coupled with introduction of Funnel charts might steer more users towards the subscription model. In this post we will review the IFS and SWITCH functions. Let’s say goodbye to nested if functions , we have already discussed on this blog.
Using Nested If Statements.
A reader asked me to explain logic behind the formula used in my Holiday Email post. That solution involved using multiple IF functions as arguments within another IF function, technique also known as nested IF logic. I find that the best way to use nested if functionality is through using decision trees AND Excel’s VBA syntax. Charting a decision tree will help us better understand the task at hand, and putting various conditions and the ways to handle them in writing, will prepare us to enter necessary formula in Excel.
Let’s suppose that we need to implement the following logic with 4 possible scenarios. If the value of cell 1 is string “Case 1” AND cell 2 is string “Case 2”, we need the output to be 4. If cell 1 equals “Case 1”, but cell 2 does NOT equal “Case 2”, we need the output to be 3. If cell 1 does NOT equal “Case 1” , but cell 2 equals “Case 2”, the output should be 2. Finally, if cell 1 does not equal “Case 1”, NOR cell 2 equals “Case 2”, the output is 1. Hopefully, using the decision tree below makes it easier to conceptualize these scenarios.
Creating user-defined Excel functions (UDFs) – Temperature Conversion and BMI Calculation functions from scratch.
Our last two posts focused on using, or working with somebody else’s VBA code. This article will focus on creating UDF functions from scratch. We will start with a simple temperature conversion from Celsius to Fahreheit, and vice versa. We will then calculate metric Body Mass Index (BMI), graduating to an imperial BMI calculation with 3 variables.
Since we’ve already worked with VBA code, we know some basic syntax rules for our future functions:
1. Start out with
Option Explicit statement.
Note : Option Explicit statement forces explicit declaration of our variables, and gives us an error message if we have a typo instead of performing incorrect calculations.
2. Define our function name and its parameter(s)
Function FunctionName(Parameter DataType) As DataType.
Note : VBA makes definining data types optional, but it is a good habit to have.
3. Perform our calculations:
Your code goes here.
4. Exit function and return it’s value:
Creating user-defined Excel functions (UDFs) – Part 2. Improved VBA code to spell a number in a text form.
In the previous post, we created Excel UDF, which translates a number into a text form, using Dollar/Cents as default currency type. We strictly relied on VBA code snippet, published on Microsoft’s knowledge base. While working with this code, we now realize that several enhancements to this UDF would be desirable.
First of all, when dealing with even amounts, we really don’t see a point of quite redundant“No Cents” message. Secondly, it appears that this code simply formats the amount specified, instead of rounding it. As an example $ 1,256.8789 will be spelled out as One Thousand Two Hundred Fifty Six Dollars and Eighty Seven Cents, but it really should be: One Thousand Two Hundred Fifty Six Dollars and Eighty Eight Cents. Next, we probably want to change our function name to reflect the fact it only translates dollar amounts, not any other currencies, nor non-currency amounts. This is definitely a matter of personal taste, but let’s call this function SpellNumberDollar and while at it, let’s change it’s input variable from MyNumber to Amount. Let’s then create a new universal UDF (SpellNumberCurrency), which could accept any currency type, instead of limiting ourselves to dollars. After all, we don’t have anything against Euros, do we? Finally, let’s write a true SpellNumber function, to spell out a non-currency number up to the third decimal point (thousandths), instead of current precision of two decimal points (hundredths.)