Lesson 6 – Using Functions & Formulas

In Lesson 5 you learned about the general rules for good spreadsheet design, regardless of whether you’re working in Excel or another spreadsheet application. In that lesson you walked through the steps to designing a worksheet, beginning at the design phase, then building your base elements (row & column headers), followed by populating the worksheet with some sample data. After that you saw how to use the AutoSum Wizard to add some relatively simple formulas to the worksheet. The natural progression to that lesson is to introduce you to the more esoteric topic of Functions and Formulas. While the AutoSum Wizard can certainly do a lot for you, and you will likely come to rely on it in your day-to-day Excel activities, there is a whole world of Functions out there that the AutoSum Wizard will never touch, so we’re going to devote this lesson to some of Excel’s more commonly used Functions, and Formulas. We’re not going to overwhelm you with obscure and industry specific functions, like those you might use in Statistical, Scientific or Engineering applications, but we will talk about some of the more advanced functions that once you learn how to use you’ll wonder how you ever did without. We’ll also be discussing some of the logic that goes into functions and how you evaluate certain scenarios. Some of it might smell a bit like high school Algebra, and to a certain extent it is, because you’ll learn how to evaluate conditions and return different answers based on those conditions.

Remember from the last lesson that a Function in place and receiving input is a Formula, but a Formula doesn’t necessarily have to contain a Function. It can be a hard concept to grasp at first, but once you start working with Functions and Formulas a bit more you’ll see how they are truly complimentary. A Function is any pre-programmed function that performs some mathematical or logical task in Excel based on input or argument that you supply to it. Functions are generally intrinsic to Excel, but you can also build your own (in much the same fashion that Microsoft builds them). A formula is either a Mathematical or Text equation (=1+1, =A1+B1, =”Daily Sales “&A1, etc.), or it can be a function, or a compilation of functions (=SUM(A1:B1) or =SUM(A1:B1)/SUM(C1:D1) or even something like =SUM(A1:B1)*.0825). Essentially if you can think of a way to evaluate data then you can probably combine some type of logic in Excel to do it. In fact, if you start getting curious about more advanced functions and look for help in Excel message boards on the Internet you will be truly amazed at some of the formulas that people can create. If you can think it, someone can probably come up with a formula to do it. Why emphasize this issue? Because you will (not might, but will) become frustrated at some point or another when you’re building formulas, that is just a statistical reality. In fact, it can be a fairly regular occurrence, but you shouldn’t let that be a deterrent. Some people will have a much easier time grasping formulas than others, but rest assured, even the most experienced Excel users on the planet struggle with formulas from time to time, so you are certainly not alone. As soon as you let yourself know that it’s OK to get frustrated, then you’ll generally be much better prepared to tackle advanced formulas.

First we need to review the Functions that are on the Formula Ribbon and explain what each group does. We’ve briefly discussed some of this in the Ribbon lesson, but now we’re going to get in depth with each one.

 

Figure 231  

Insert Function

If you don’t know what Function you want off the top of your head, or even what category to place it in, then you come here.

• • You can enter your search term and Excel’s help will do what it can to match your request with the best result. Note that the default category is “Most Recently Used”, so make sure to change that to “All” before you start, otherwise you’ll limit your results. On the other hand, if you know you’re looking for a Function in a particular category, you may well want to narrow the list. Unfortunately, the Insert Function Wizard won’t always find what you’re looking for, especially if you don’t know what to look for in the first place, so it is somewhat of a Catch-22 tool; you have to somewhat know what you’re looking for in order to find it, but if you don’t know what that is, then how do you know what to ask it to look for? Confusing isn’t it? That would be one of those times you turn to the Internet and ask a question (the message board at http://www.mrexcel.com is probably the best place to look if you’re stumped).

 

 

Figure 232  

• • If you do choose a Function from the Insert Function Wizard, then it will bring up another dialog that can help you put in the correct values or cell references, and each of the function’s arguments have some help text beneath the dialog input boxes. Another benefit to this Wizard is that it will evaluate each step of the function for you so you can immediately see if you entered an argument incorrectly. If the Wizard just doesn’t do enough for you to figure out how to build a function, you can always click on the “Help on this function” hyperlink to get more details.

Function Library

 

Figure 233  

• • AutoSum - As you saw in the Lesson 5 examples, AutoSum is incredibly easy to use. Just put your cursor beneath or next to the range you want to evaluate, choose your function from the wizard and let it do its thing. It’s not infallible and won’t evaluate data with gaps in it, but it does a pretty good job. Although your intrinsic functions are limited to S, Average, Count Number, Max & Min, you can select the More Functions option which is going to launch the Insert Function Wizard (yet another one of those things that Microsoft exposes in multiple places). You’ll find examples of all the AutoSum functions in the Lesson 6 companion workbook.
• • Recently Used – This is a faster way to get to your Recently Used functions than having to launch the Insert Function dialog. Just click and there’s your list.
• • Financial – This and the rest of the Function Library menu items function the same way; they’re each going to display an alphabetical list of the functions specific to that category. Financial functions can be very powerful tools for business, because you can perform so much detailed analysis. Like determining the depreciation of a piece of equipment you are thinking about purchasing, or even determining what your monthly payment might be based on amount down, interest rate, etc. They are generally of much more use than trying to figure it out on a calculator, especially because you can reuse them. Some examples of Financial functions are:
• • FV – Future Value
• • IRR – Internal Rate of Return
• • NPV – Net Present Value
• • SLN – Straight-Line Depreciation
• • Logical – Logical functions allow you to evaluate certain criteria and return one result if the evaluation is true, and another if it’s false (e.g. =IF(A1=”Yes”,1,2), which simply says “if A1 = Yes, then return a 1, otherwise if A1 is anything other than “Yes”, return a 2). Logical functions include:
• • IF – IF statements are the cornerstone of logical analysis in Excel. We will be going into these in depth.
• • AND – Both AND & OR allow you to add multiple criteria to IF statements. E.G. IF something is true, AND something else is false, then…Or IF something is false, OR something else is true, then…
• • OR
• • NOT – This evaluates if something is NOT equal to a condition. E.G. NOT = 1.
• • TRUE/FALSE – Called Boolean evaluations, these simply test if something is True or False?
• • IFERROR – This can be used to remove error notices from formulas that return errors. It’s generally not advisable when you’re first building models, because it hides all errors, but it doesn’t resolve an error, so you might not know a formula isn’t working. However, it is very common to use prior to distribution because users who see formula errors usually assume something is broken, and when they assume something is broken, they’re less likely to use your workbook.
• • Text – These let you work with Text in formulas. Some Text function examples are:
• • CLEAN – Removes not-printable characters from a reference. This often comes in handy when copying data from websites or databases.
• • FIND – Find a text or numeric value in a text string
• • LEFT – Return x characters from the Left of a string. E.G. =LEFT(“ABCDE”,2) will return “AB”.
• • RIGHT - Return x characters from the Right of a string. E.G. =RIGHT(“ABCDE”,2) will return “DE”.
• • MID – Returns x characters from the middle of a string given a starting point. E.G. =MID(“ABCDE”,2,2) will return “BC”.
• • TEXT – Allows you to format numeric values when they’ve been concatenated with text. E.G. =”Today’s date is: “&TEXT(A1,”MM/DD/YY”). Where A1 is a valid date the formula would return “Today’s date is: 01/15/11”.
• • TRIM – Removes leading and trailing spaces from text strings. It will not remove spaces between words.
• • UPPER – Converts all text to Upper case. There are also LOWER & PROPER functions.
• • Date & Time – As it might sound, these let you work with Dates & Times largely from a mathematical standpoint. E.G. determining the number of days between two dates. Some examples are:
• • TODAY – Returns Today’s Date
• • NOW – Returns the Date & Time
• • DAY – Returns the day number of the month, from 1 -31.
• • NETWORKDAYS – Returns the number of standard workdays (Monday-Friday) between two dates, including system recognized Holidays.
• • YEAR – Returns just the year from a date reference. E.G. =YEAR(TODAY()) would return “2011”.
• • Lookup & Reference – Lookup & Referential formulas are perhaps the most widely used outside of the data summarization functions (SUM, AVERAGE, COUNT, etc.). They let you look up values in a list and return relevant information from the list. For instance you might want to look up a customer name to get their address or phone number. Lookup & Referential functions let you do that without having to go to the source to look it up manually.
• • Math & Trig – While some of the functions in this category are definitely reserved for those in a business that deals with Math & Trigonometry calculations, there are quite a few that you’ll frequently use in the course of business:
• • ABS – Returns the absolute value of a number, which is the number without its sign. E.G. =ABS(-10) is 10.
• • EVEN/ODD – Rounds positive values up to the nearest Even integer, and negative numbers down. Odd does the opposite.
• • CEILING/FLOOR – Rounds a number up to the nearest number you specify. E.G. =CEILING(2.6,0.25) would be 2.75. This is good if you’re pricing to the nearest x increment. FLOOR does the opposite.
• • INT – Rounds a number down to the nearest integer. E.G. =INT(2.7) is 2.
• • RAND – As you saw in Lesson 5, this generates a random number between 1 and 0. Remember to get meaningful numbers for testing you’ll probably want to multiply RAND by a factor of 10. E.G. =RAND()*100.
• • ROUND – This rounds a number to a specified number of digits. E.G. =ROUND(1.234,2) is 1.23
• • SUM – Returns the Sum of a range. You’re already familiar with this one from Lesson 5.
• • SUMIF – Returns the Sum of a range given a single criterion. E.G. =SUMIF(A1:A10,”YES”,B1:B10), would return a sum of B1:B10 where the cells in A1:A10 = YES.
• • SUMIFS & SUMPRODUCT – Both of these return the sum of a range based on multiple criteria. You’ll find examples of both in the Lesson 6 companion workbook.
• • More Functions – This exposes several more categories of functions that are generally specific to a particular field, although the Information functions are used quite regularly in evaluating ranges. E.G. =ISNUMBER(A1) would return a True/False depending on the value ion A1. This is a very useful function, because sometimes numbers may appear to be numbers when they are actually seen as text. In cases like this your formulas won’t calculate correctly, so ISNUMBER helps you track down the improperly formatted range.
• • Statistical
• • Engineering
• • Cube
• • Information
• • Compatibility

Order of Operations

Before we start exploring some of Excel’s in-depth functionality with regards to functions and formulas, you need to understand how they calculate. Excel follows a mathematical order of Operations when it calculates your functions and formulas. The Primary order of operations is as follows:

• • Parentheses
• • Exponents
• • Multiplication/Division
• • Addition/Subtraction

 

• • Both Division/Multiplication and Addition/Subtraction carry the same weight, so if either are in the same formula, they will be carried out by whichever comes first. However, you can alter the order of any operation by adding parentheses. This is where those high school Algebra concepts will kick in.
• • The formula =2*3+4 will result in 10, because 2*3 = 6 and 6 + 4 = 10.
• • However = 2*(3+4) = 14, because 3+4 = 7, and 2*(7) = 14. In this case, since the 3+4 was in parentheses it gets calculated first.
• • Order of Operations doesn’t take that long to grasp, but you need to be aware of it, because if you have an obscure calculation that doesn’t generate the correct results, then a lot of other dependencies that are directly or indirectly based on that calculation can also be off. Generally, those small errors are like trying to find the proverbial needle in a haystack, so it’s better to make sure you get the calculations right the first time.

Entering Functions

Entering functions is fairly straightforward. If you’re beginning from the Function Wizard, then you let that do all of the work for you. Here’s an example using the Depreciation example you’ll find in the Financial section of the Lesson 6 workbook.

 

Figure 234  

 

Figure 235  

• • As you can see, the primary cells we’ll be referencing are N2:N4, and we want the depreciation calculations to be returned for Years 1-5, so we’re going to be referencing M7:M11 for our Periods. First invoke the Formula Wizard and select the DB function. In the Cost argument, click the Range selection button (circled in red in the example), then click on cell N2. For Salvage repeat and click on N4, then for Life, click on N3. Then Period will be M7, and enter a 12 in Month (you can leave this blank if you want, as 12 is the default value for the Month argument). Notice how the Function Wizard already added Absolute References for you where they were needed. As you confirm each step, you’ll see the Function Wizard evaluate it to the right of the argument. When you enter the last argument, the full function will evaluate. This is good, because you’ll see if you get a valid or expected result. If you don’t then you know that you have some changes to make. Next you’ll see the Function Wizard after it’s taken all of the function arguments and has calculated a result.

 

Figure 236  

• • If the result is what you’re expecting, then go ahead and confirm the formula. Now you can copy Year 1’s result down to Year 5 and you’ll have your extended 5-Year depreciation results.
• • As you start getting comfortable with multiple argument functions, this is probably the way that you’ll want to enter them. Range only functions (like =SUM(A1:A10)) you can probably just write by hand or use the AutoSum Wizard, although it is good practice to start learning how to enter functions by hand, because it definitely has some speed advantages.

 

Figure 237  

• • If you’re entering functions or formulas by hand then all you need to remember is to preface the function/formula with an equals (=) sign. That tells Excel that you are entering a Function/Formula. And don’t forget that the Function Wizard isn’t the only place that’s helpful when you’re entering Functions; as long as Excel recognizes the Function that you’re entering it will give you a control tip text once you’ve entered the opening parenthesis for the Function. Each function argument will be in bold in the control tip text until you confirm it with a comma and move on to the next, at which point that argument will be bolded.
• • When you’re entering functions/formulas by hand and you need to select a range to feed to the function, you can navigate there with the mouse, or with the arrow keys. It’s just personal preference, although you’ll find times where one is more advantageous than the other and vice versa; it really depends on where the function is in relation to the range you’re trying to pass to it.
• • Formula views – Don’t forget, you can view and edit your formulas in both the Formula bar and in the cell, and in either case you’ll get those colored references that correspond between the range references in the formula and the ranges on the worksheet.

 

Figure 238  

Excel Function/Formula Errors

As you’ve seen, when you properly enter a function/formula, Excel gives you a result. But what happens if you enter a function/formula incorrectly? Well, as picky as Excel is, it’s going to tell you all about it, by displaying an Error message in the cell. Some error messages are fairly straightforward and can be fixed right away, while others are a bit more obtuse, and can be difficult to track down (especially if a change you made on a different worksheet causes an error and you don’t see it until much later). You’ll find that error management often means using Logical statements to evaluate the function and/or the error to resolve it. Errors play a big role in Excel development, as they can quickly point out problems that you need to resolve. Unfortunately, many people throw a blanket error management solution at everything, which can be misleading because it prevents Excel from telling you some thing’s wrong. Before you start using error management practices, make sure that you understand errors, what causes them and how to sort out the root cause before using a shotgun approach.

 

• • #DIV/0! – You can’t divide by 0 (not in Excel, or math for that matter). This is most often caused by a formula like this: =A1/B1, where B1 isn’t necessarily a 0 value, it just hasn’t had anything entered into it yet, so it evaluates to an error. The easiest way to fix this one is to test for the existence of the denominator; if it exists, then complete the formula, if not then return a 0 or blank value. E.G. =IF(B1,A1/B1,0) or =IF(B1,A1/B1,””).
• • #N/A – This means that a referential formula can’t find what you told it to find. Most people will address this error with ISERROR or IFERROR. An only slightly better approach is to test directly for the NA condition, because it still requires double calculation. E.G. =IF(ISNA(FUNCTION),0,FUNCTION)).
• • #NAME? – This means that Excel doesn’t recognize the function name. Generally this is the result of a typo, like =sume(a1:a10), where “sume” should have been “sum”.
• • #NULL! – This happens when you tell a function to intersect two ranges that can’t physically intersect. E.G. =(A1:C1 B1:B5) will return the value in cell B2 because that’s the intersection between the two ranges. But =(A1:C1 B2:B5) will return a #NULL! Error because there is no intersection between the two ranges (row 1 can’t intersect with rows 2-5).
• • #NUM! – This happens if you try to pass a non-valid number to a function, or if you try to get Excel to calculate a number that’s too big or small. =1*10^308 would cause an error, because the largest number Excel can calculate is =1*10^307.
• • #REF! – This generally happens if you’ve deleted a range that a formula referred to and have severed the link. If you have this formula: =Sheet2!A1 and delete row one on Sheet2 you’ll get this error: =Sheet2!#REF!.
• • #VALUE! – This happens if you try to compare two different operands, like trying to multiply a value by text. =(A1*A2) would give you an error if one cell was numeric and one was text.

Commonly Used Business Functions

The rest of this lesson will be sharing and explaining function examples with the Lesson 6 workbook. We’ll discuss where and when you might use these functions and then show you how to set up a scenario to use them, followed by actually putting them together. You’ll probably spend a lot of time switching between the lesson and the companion workbook, because it’s generally better to be hands on when you’re learning functions and how to use them. We’re going to go back through the Functions in the same order they are on the Formula Ribbon and use the top four or five from each category, as well as some beneficial formulas. Unfortunately, this is by no means an exhaustive list of all of Excel’s functions, but there will be some Microsoft references in the Lesson Summary.

 

• • Financial – There are plenty of functions in the Financial category that can help you manage your business, and there are many that you’ll never even get close to using. While some of them are relatively complicated and do require some time to learn the ins-and-outs of the business’ they target (like stocks and banking), we’re going to discuss some of the Financial functions that might be the most meaningful to you in the course of normal small business operations. Fortunately, these are no less powerful tools for you than some of the functions a trading analyst might use.
• • FV – Future Value – Let’s say you want to start an investment account. If you can get 3.2% Interest over 60 months and you put in $650/month, how much will your investment be worth at the end of 5 years? Note this is a simple example and doesn’t include compounding or variable interest rates or deposit amounts.
• • =FV(Rate, Nper, Pmt,[PV],[Type])
• • Rate – Interest Rate/Number of Annual Payments
• • Nper – Number of Periods in the Investment
• • Pmt – Payment made each Period (this is a negative amount – think of it as the amount that comes out of your checking account to go in the IRA)
• • PV - Present Value of the Investment – it can be 0, or left out.
• • Type – 1 or 0 – Indicates whether the Payment is made at the Beginning or End of a Period (Optional)

 

Figure 239  

• • NPER – Number of Periods – This will tell you how many payments you have to make over the life of an investment if you know the interest rate and payment amount. For instance if you wanted to buy a new copier and it’s $5,500. If you know the Interest rate and payment periods (monthly, quarterly, etc.) then you can figure out how many payments you’ll need to make before you pay it off.
• • =NPER(Rate, Pmt, Pv, [Fv], [Type])
• • Rate – Interest Rate/Number of Annual Payments
• • Pmt – Payment made each period
• • PV – Present Value
• • FV – Generally 0 (Optional)
• • Type – 1 or 0 – Indicates whether the Payment is made at the Beginning or End of a Period (Optional)

 

Figure 240  

• • PMT – Payment – This will tell you what your payment is if you know the amount you want to finance and the interest rate. It’s similar to the NPER function, just the other way around. In this case you know your copier is $5,500 and the interest rate the bank gave you, but you need to figure out if you can afford the monthly payments. If you compare the results from this function to the NPER function we just did you’ll see that they’re essentially just reverse cases of each other.
• • =PMT(Rate, Nper, PV, [FV], [Type])
• • Rate - Interest Rate/Number of Annual Payments
• • Nper– Number of Periods in the Investment
• • Pv – Present Value
• • Fv – Future Value
• • Type – 1 or 0 – Indicates whether the Payment is made at the Beginning or End of a Period (Optional)

 

Figure 241  

• • What-If Analysis
• • We’re going to take a slight detour here and show you one of Excel’s Data Analysis tools, because it fits in with the calculations that we’ve been doing. Going back to the new copier example: you have your Interest Rate from the bank, you know what the copier costs, you know the number of months it’ll take for you to pay it off, and you know the payment amount. But what if the payment’s just a bit too high? In this case your copier payment is about one hundred bucks a month, but you want to know how much copier can you get for $75? So how do you do that? You break out the What-If tools. In this case we’re going to use what’s called Goal Seek. We’ll take an existing model, the PMT model, plug in $75, and Excel’s going to run through scenarios in its head until it gets to the copier that you can afford for $75/month.

 

Figure 242  

• • In this case we want Excel to change the Payment to $75, by adjusting the copier price until it gets there, so plug in the reference/value and watch what happens.

 

Figure 243  

• • So now you can see that at $75/month, with those terms, you can afford a copier that costs $4,153. If you can have this information in hand before you start negotiating for that copier, then you’ll be in a much stronger position. This works with buying cars too. In fact, you can figure out any purchase that has terms involved.
• • (Ok, we’ll now return to our regularly scheduled lesson…)
• • RATE – Interest Rate per Period – This will tell you what your Interest Rate is if you know the payments, the term of the loan and the Present Value of the loan. This can be used to find out what credit is really costing you.
• • =RATE(Nper, Pmt, PV, [FV], [Type])
• • Nper– Number of Periods in the Investment
• • Pmt – Payment made each period
• • PV – Present Value
• • FV – Generally 0 (Optional)
• • Type – 1 or 0 – Indicates whether the Payment is made at the Beginning or End of a Period (Optional)

 

Figure 244  

• • DB – Straight Line Depreciation – If you were to buy a piece of equipment, then you want to know how much you can depreciate it each year during its expected life cycle. This not only helps with taxes, but it also helps you figure in operating expenses for that equipment.
• • =DB(Cost, Salvage, Life, Period, [Month])
• • Cost – the purchase price of the equipment
• • Salvage – When it’s time to get a new one, how much can you get if you sell this one?
• • Life - How many years can you expect to use the equipment before you need to replace it?
• • Period – Generally measured in years for large equipment purchases.
• • Month – How many months in the first year – (Optional) – the default is 12 months if you leave it out.

 

Figure 245  

• • Logical
• • IF – As mentioned several times, IF statements are the cornerstone to a lot of Excel logic. As you get more and more comfortable with Excel you may well find the ubiquitous IF statement being one of the functions you draw on the most. But the IF statement comes with some significant warnings that we’ll get into shortly. Just as Excel has over a million rows, but using them all probably isn’t a good idea, the IF statement can do a lot of things, but trying to make it do everything isn’t a good idea either. We’ve already mentioned how earlier versions of Excel allowed you to nest up to 7 IF statements, and here’s a very simple example.

=IF(A1=1,1,IF(A1=2,2,IF(A1=3,3),IF(A1=4,4),IF(A1=5,5))))

• • Try reading that if it contained some truly complex comparisons!

 

• • One of the most common uses of IF statements is to return data that matches specific criteria, or falls within a range of values. Here is a relatively common example of a nested IF statement to return letter grades based on test scores (which you’ll see in the first Logical formula example in Lesson 6 workbook):

 

Figure 246  

=IF(A1>=90,”A”,IF(A1>=80,”B”,IF(A1>=70,”C”,IF(A1>=60,”D”,IF(A1<60,”F”)))))

• • Another is calculating commission statements:

=IF(A1>25000,A1*2%,IF(A1>15000,A1*1.5%,IF(A1>5000,A1*1%,0)))

• • Note that in both formulas, the criteria need to be ordered sequentially in order for the formula to calculate correctly. So in the first example A1>=90 gets evaluated first, and if that condition is true, then the formula performs the calculation associated with that condition. If the condition isn’t true, then it moves onto the second, and so on. But if you get your conditions out of order, then one condition can invalidate the next and render your formula(s) useless. One of the inherent weaknesses with IF statements is that they need to be precise and ordered.
• • While both of these formulas work fine, they’re unwieldy and should be avoided if at all possible. Why? Primarily because the data in the formulas is static, so if the conditions driving the formula ever need to be changed, the formula needs to be manually adjusted as a result. Granted, the grades example isn’t too bad, because it’s not likely those grade standards will change too often, but just imagine how much work you’d have to do if you have a lot of formulas like the commission example, and you have to change the criteria? Ideally, you’ll get into the habit of only using IF statements for Text comparisons like the earlier example. Yes/No/Maybe or Male/Female evaluations are very common, and the nice thing about them is that criteria aren’t likely to change very often. If you find yourself with situations like this then by all means use IF statements, otherwise it’s time to move up to more robust alternatives, starting with LOOKUP, which we’ll get to shortly. If statements also work well for evaluating Conditional Formatting criteria, which we’ll be discussing in the next lesson.
• • One of the primary reasons to move away from IF statements for multiple criteria is so that you can use “table based” reference data. This gives you the ability to have your data points on a worksheet, where the values can be easily changed, as opposed to hardcoded in a formula, where changing the values can be a challenge. Many workbooks have broken because of numerous IF statements that someone didn’t update. In the commission example above, what would happen if you needed to change the 2% and you had hundreds of formulas depending on that one? It wouldn’t be fun, and that’s a relatively small example. Imagine one with 64 conditions! And that’s if you can even find the formula in the first place! Table based dependencies are much easier to change on the fly, which can mean a lot especially if you’re dealing with complex models and testing multiple criteria.
• • You will find multiple examples of logical function combinations in the Lesson 6 workbook. As with so many things in Excel, the variations you can come up with are virtually limitless.
• • AND - This returns TRUE if all of the arguments being evaluated are TRUE. If one or more of the evaluated arguments are FALSE, then AND returns a FALSE.
• • OR – This evaluates all of the arguments and will return TRUE if any of them is TRUE. It will return FALSE ONLY if all of the arguments are false.
• • NOT – This simply changes TRUE to FALSE or FALSE to TRUE. It is good if you are trying to exclude something from a comparison.
• • IFERROR – As mentioned, this is strictly for error management, and remember it will suppress ALL errors, so it’s generally not a good idea to use until you know that your model is functioning properly.
• • Text – (Text functions generally go hand-in-hand, so examples will be after their explanations).
• • FIND – This finds a particular value within a string and returns an integer corresponding to its position in the string starting from the left. =FIND(“*”,A1). If * was in the fifth spot in the cell, then FIND would return a 5.
• • LEFT/RIGHT/MID – These allow you to pull text from a string either from the Left, Middle or Right. It is used a lot in both Parsing and Concatenating names. E.G. converting “Smith, Bob” to “Bob Smith”. E.G. =LEFT(A1,5) would return the 5 characters to the left of cell A1. If you were to change that to =RIGHT(A1,5) you would get the rightmost 5 characters.
• • & - While there is a CONCATENATE function, the Ampersand is much more robust, and is faster to use (see TEXT for an example).
• • TEXT – This allows you to reapply formatting to numeric values when you have joined them in a text string. E.G. =”Today’s Date is: “&TEXT(TODAY(),”MM/DD/YY”).
• • Concatenating Text - =PROPER(B2&”, “&A2)

 

Figure 247  

• • Parsing Text

 

Figure 248  

• • Last Name - =RIGHT(A10,LEN(A10)-FIND(“,”,A10)-1)
• • First Name - =LEFT(A10,FIND(“, “,A10)-1)
• • Using TEXT to format concatenated text & values - =”Report Date: “&TEXT(TODAY(),”mm/dd/yy”)

 

Figure 249  

 

• • Date & Time
• • TODAY – Simple, it gives you today’s Date. It does not take any arguments and it is volatile, meaning that it will continuously update. Some people try to use Date & Time functions for static Date/Time stamps, and then wonder what happened the next day, when the date has updated.
• • NOW – This gives you the Date and Time. It does not take any arguments and it is volatile as well.
• • YEAR – This returns the year of the date it’s referencing. E.G. =YEAR(TODAY())
• • WEEKDAY – This returns the day of the week as an integer value (1-7). E.G. =WEEKDAY(TODAY(),1) – If it was a Wednesday the answer would be 4. Note that WEEKDAY has a Return Type argument for the starting day of the week; 1 is Sunday - Saturday, 2 is Monday - Sunday, 3 is Monday – Sunday, where Monday starts as a 0 value instead of a 1. You can convert that to a Text day with =TEXT(WEEKDAY(TODAY(),1),”MMM”).
• • DAYS360 – This lets you calculate the difference in days between two dates. It’s great for kids who want to know how long it is until Christmas or their next birthday. E.G. =DAYS360(TODAY(),”12/25/2011”) Note that if you manually enter the date in the function, you need to wrap it in quotes. It’s generally easier to put the date in a cell and reference it directly.

 

• • EOMONTH – This returns the last day of the month in reference to the date entered as an argument, and how many months away you specify. =EOMONTH(TODAY(),0) would tell you the last day of the current month. =EOMONTH(TODAY(),6) would give you the last day of the month six days from now. This can be handy with project planning and calculating dates, because you can let Excel figure it out for you vs. having to enter the end date manually somewhere.
• • Lookup & Referential – These are some of the most widely used functions in Excel and once you get the hang of them you’ll understand why. Lookup functions help prevent having to enter in data repeatedly. Here’s a common scenario: a business has an invoice form they use for their goods and services. When it comes time to enter an order for a repeat customer, someone types in the Customer Name, then has to go look up all of their relevant information, like Address, Phone #, etc. Lookup functions do all of that for you; all you need is a unique identifier for the record you want to find. In the case of customers, it’s generally a Customer ID # or Business Name. You can also return information on sales reps, products sold, etc. Almost any information that’s table based can be set up to retrieve that information via formula rather than doing it manually.
• • LOOKUP – Remember the grades example with the long IF statement that determined what the resulting grade for a student’s test score was? That is the perfect scenario to explain the LOOKUP function. LOOKUP functions essentially work like this: What do you want to look up? Where do you want to look for it? If Excel finds it, what do you want it to do with it?
• • Here’s the Grades example again, but this time you’ll see a table set up beneath it that shows the Number grade scale and the corresponding Letter grade. We’re going to tell the LOOKUP formula to lookup the student grades in B2:B7 and compare those values with the value in the grades table and return the result.

 

Figure 250  

• • Here’s the LOOKUP formula: =LOOKUP(B4,$A$12:$B$16), and here’s what it says:
• • Look for what? The value in B2
• • Where to look for it? The grades table A10:B14 (note that both columns were entered, because we want the LOOKUP to give us the value from the second column).
• • What to do when it’s found? Return the corresponding value in the second column. Excel knows this because two columns were selected in the range and it will return the value in the rightmost column of the stated range.
• • LOOKUP is the simplest of the lookup functions, and the least robust, but it works perfectly in cases like this. LOOKUP tables must be sorted in Ascending order in order to work properly. That’s why you see the grades table sorted from lowest to highest. LOOKUP will look for an exact match, and if it can’t find it, it will return the largest value in the table that is Less than the lookup value. In this example, the LOOKUP found the closest possible or approximate match to the grade that was entered.
• • There is also what’s called an Array version of this function and you’ll see it here by example, but you should note the same cautions with using the Array method as you would when hardcoding any values in a function. In this case, as with the IF statement grade example, it’s probably relatively benign though, since grades and their scales aren’t likely to change anytime soon. The primary advantage to the array version is that it eliminates the lookup table.

=LOOKUP(B4, {0,60,70,80,90}, {“F”,”D”,”C”,”B”,”A”})

• • VLOOKUP – Short for Vertical Lookup, it looks from left-to-right. This is the most common of all the lookup functions, and probably one of the most widely used functions in Excel. If you begin using Excel with any regularity, you’ll probably find this to be the case with you. VLOOKUP has a degree of flexibility that LOOKUP does not in two ways: first, it does not care which way its list is sorted, and second, you can lookup a multiple column table range and tell the VLOOKUP exactly which column you want results from.
• • Here’s the Microsoft syntax for VLOOKUP:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• • Confusing isn’t it? This might be a little bit easier to understand:

=VLOOKUP(Lookup what?, Lookup where?, How many columns over should it go?, Exact or Approximate Match?)

• • Following is a common example of VLOOKUP in action. There is a simple table of Account #’s and corresponding dollar figures for each month. In A8 you can enter the value you want to find in the top table, which is “456” in this case. We want to search the entire table range (A2:M4), and in January’s case, we want the second column to the right, which is the “,2,” part. FALSE (or 0) indicates that VLOOKUP should only return an exact match. As you might guess, when you copy the function to February you need to change the “,2,” to “,3,” and so on.

 

Figure 251  

 

• • HLOOKUP – You guessed it, this is short for Horizontal Lookup. This works the same as VLOOKUP, except it looks from top-down instead of left-to-right.

 

Figure 252  

• • In the Gross Profit example the function is =HLOOKUP($B5,$A$7:$F$16,4,FALSE), so it’s looking for the value in B4 (Qtr3), in the range A7:F16, and when it finds it, the function will offset 4 rows to the Gross Profit row. Note that the function starts at A7 instead of B7. If you were going to use the standard HLOOKUP, then B7 is where you would start, but as you’ve just seen it’s not very dynamic is it? First you have to figure out which criteria you want to return (Gross Profit), then you have to count how many rows down the value will be given where the header row (Qtre1-Qtr4) is, in this case it’s 4. But what if you have a worksheet that’s hundreds of rows? You’ll be there all day trying to match up all of your rows and you’ll probably make a mistake. In this case you can use the MATCH function to make the HLOOKUP dynamic by finding the “Gross Profit” row for you.

=HLOOKUP($B5,$A$7:$F$16,MATCH(C4,$A$7:$A$16,0),FALSE)

• • =MATCH(C4,$A$7:$A$16,0) finds Gross Profit on the 4th row in the range, so it feeds a 4 to the HLOOKUP. MATCH works by looking in A7:A16 for the value that’s in C4. The 0 means that you want MATCH to only return an exact match. If you were to put a 1 there, MATCH would find the first approximate match.
• • INDEX/MATCH – This is one of the most powerful function combinations you have. Have you noticed any potential shortcomings with VLOOKUP & HLOOKUP? They can only go left-to-right, or top-down. Meaning that whatever value you want to look for needs to be in the very left most, or top portion of the table range that you’re going to search. In the Grades example that’s fine, but what if you want to do something like search an employee table where Social Security Number is the unique identifier, and it’s in the middle of the table where you need information to the left of it? The lookups can’t do it. Some people will try to trick Excel by using what’s called a “helper column” (and they are sometimes necessary, but in this case it’s not). To use a helper column you would insert a column at the very left of your table and set a reference in that column to your unique identifier in the middle of the table. So let’s say your SSN’s are in column F, you would add a column in A and use =F2 to put SSN’s in column A, then go about your way with VLOOKUPs (which will work fine by the way). But this approach is inefficient and error prone. What if you add more employees to the table? You have to remember to update that helper column as well. It’s something that’s often overlooked and leaves people scratching their heads because they can’t figure out why the formula’s not working, even though they know the information’s there.

 

• • So this is where you can combine a few functions into a dynamic formula.
• • MATCH - As you saw in the dynamic HLOOKUP example, MATCH looks for a value and tries to match it in the range that you specify. What’s nice about MATCH is that you can have it search rows where it will return the row number of the item it finds (relative to where it’s searching). Match can also work horizontally, looking for matching values in columns and returning the relevant column number (1, 2, 3, etc.). So if you can match an item in a vertical range, and then match another item in a horizontal range, you can get the intersection of those two ranges. In the HLOOKUP example, we could look for Gross Profit in A7:16 (4), then look for Qtr3 in A7:F7 (4), then look for the 4th row and 4th column in A7:F16. OK, so we’ve got our row and column numbers, and we know the range to use to find the intersection of the 4th row & 4th column. But how do we tell Excel to use them? This is where INDEX comes in.
• • INDEX – The returns a value within a given range based on the intersection of the row and column that you specify. =INDEX(Range you want to look in, Number of Rows, Number of Columns). In our case we want this:

=INDEX(A7:F16,4,4)

• • But there we go with those static arguments again. Now you substitute the static value for the values that MATCH returned, and you have this:

=INDEX($A$7:$F$16,MATCH(C19,$A$7:$A$16,0),MATCH($B20,$A$7:$F$7,0))

 

Figure 253  

• • Yes, that’s a lot to absorb, but you will find these examples in the companion workbook along with a breakdown of how to get there. There is also a worksheet dedicated solely to INDEX/MATCH and how to build the final formula in pieces. When you get comfortable with INDEX/MATCH you might find yourself completely ignoring the LOOKUPs. Between VLOOKUP & HLOOKUP, the latter is the least robust, and often gets replaced with INDEX/MATCH anyway.

 

• • Math & Trig - From a business perspective, the ability to conditionally Average, Count and Sum your data is a fantastic analytical tool, as well as a timesaver. In the past if you wanted to analyze your data by different criteria you either did it by hand or used a database application. But with this amazing functionality you have all of those tools right at your fingertips! Note that for simplicity’s sake, none of the formula examples have been cluttered with Absolute References, but when you use them in real-life scenarios it’s something you can’t forget.
• • SUMIF – This is pretty much exactly what it sounds like. It lets you sum one range based on criteria in another. For instance let’s say you want to sum all sales for the month where the item sold was blue. In this example we’re going to Sum all values in column C where the corresponding value in column A is “Yes”.

 

Figure 254  

• • SUMIF works like this: =SUMIF(The range with the Sum criteria, the Criteria, the range with the values to be summed)

=SUMIF(A2:A17,E2,C2:C17)

• • Unfortunately, it reads a little bit backwards: Sum the range C2:C17, where A2:A17 equals the value in E2, in this case, “Yes”.
• • SUMIFS – This is similar to SUMIF, except it lets you sum by multiple criteria.

 

Figure 255  

=SUMIFS(C2:C17,A2:A17,E2,B2:B17,F2)

• • Oddly enough, the arguments in SUMIFS aren’t in the same order as they are in SUMIF: =SUMIFS(Range to Sum, 1st Range to Evaluate, 1st Criteria, 2nd Range to Evaluate, 2nd Criteria). In this case Sum C2:C17 where A2:A17 equals the value in E2 (Yes), and where B2:B17 equals the value in F2 (Blue). SUMIFS will support up to 127 different criteria pairs, but imagine trying to read that formula!
• • SUMPRODUCT – This can be an incredibly robust, but complicated function, and it can handle up to 255 arguments. As you’ll see in the example, SUMPRODUCT here returns the same value as SUMIFS. This lesson isn’t going to go into detail about how to create SUMPRODUCTS, because it is not a one-size fits all function. It is here, however, so that you know it’s available. Many times analysis that seems impossible can be returned with SUMPRODUCT.
• • SUMPRODUCT breaks down the arguments that you specify, multiplies them and generates the results as the sum of those products. It’s a bit easier to see if you invoke the Function Wizard and watch how Excel is evaluating the formula. In this case, SUMPRODUCT is evaluating A2:A17 where the values equal E2’s value, then evaluating B2:B17 where the value equals F2, finally coming to the Sum range in C2:C17.
• • In the Function Wizard you can see that SUMPRODUCT evaluated the results of the arguments as {23;0;0;0;99;0;0;0;61;0;0;0;95;0;0;0}, which equals 278 when summed.

 

Figure 256  

• • COUNTIF – The COUNTIF(s) & AVERAGEIF(s) are the same as SUMIF, but return a count or average of the items in a range vs. their sum. The “IF” versions will only accept a single criteria, while the “IFS” versions will accept multiple. =COUNTIF(Range to Count, Criteria to Count by).

 

Figure 257  

=COUNTIF(A2:A17,E2)

• • In this case count the values in column A where the value = “Yes”.
• • COUNTIFS – =COUNTIF(1st Range to Count, 1st Criteria, 2nd Range to Count, 2nd Criteria). COUNTIFS will only count items where both ranges meet the criteria. In this case it will only count the values that are “Yes” AND “Blue”.

 

Figure 258  

=COUNTIFS(A2:A17,E2,B2:B17,F2)

• • AVERAGEIF - =AVERAGEIF(Range with the Criteria, Criteria, Range to Average).

 

Figure 259  

=AVERAGEIF(A2:A17,E2,C2:C17)

• • In this case Average C2:C17, where the cells in A2:A17 equal E2 (“Yes”).
• • AVERAGEIFS - =AVERAGEIFS(Range to Average, 1st Criteria Range, 1st Criteria, 2nd Criteria Range, 2nd Criteria)

 

Figure 260  

=AVERAGEIFS(C2:C17,A2:A17,E2,B2:B17,F2)

• • This will Average the values in C2:C17, where the cells in A2:A17 equal the value in E2 (“Yes”) AND the cells in range B2:B17 equal the value in F2 (“Blue”)
• • 3D Formulas – These are a way to perform calculations on multiple worksheets at once without cumbersome manual formulas. Imagine the first month model for a 12-Month Cash Flow summary we put together in Lesson 5? If you had all 12 Months in place and a summary worksheet, you could summarize the total like this:

=January!A1+February!A1+March!A1+April!A1+May!A1+June!A1+July!A1+August!A1+September!A1+October!A1+November!A1+December!A1

• • Which means either typing that formula by hand (not fun), or entering = in the summary sheet, click on January, then A1, add a +, then February and A1, etc. That’s no fun either. This is where 3D formulas come in especially handy. You could replace that monstrosity above with this:

=SUM(January:December!A1)

• • That will sum cell A1 in January, cell A1 in December, AND cell A1 on every sheet in between those two sheets.

 

Figure 261  

• • Here is an example from the companion workbook, where there are worksheets named 3D First (the worksheet in the example), then 3D Second, 3D Third and 3D Last. So the formula in the example is summing cell B4 on 3D Second, 3D Last, and what’s in between them. You’ll also see an example of a 3D AVERAGE in the companion workbook.
• • Excluding statistical functions, the following functions can support 3d Formula syntax:
• • AVERAGE
• • AVERAGEA
• • COUNT
• • COUNTA
• • MAX
• • MAXA
• • MIN
• • MINA
• • AND
• • OR

Unit Summary: Lesson 6 – Using Functions & Formulas

• • In this lesson you learned about business centric functions in each of the Formula Ribbon’s categories:
• • AutoSum
• • Recently Used
• • Financial
• • Logical
• • Text
• • Date & Time
• • Lookup & Reference
• • Math & Trig
• • More Functions
• • You learned about the esoteric differences between Functions and Formulas, where Functions result in Formulas, but Formulas don’t necessarily need to include Functions.
• • You learned about Excel’s Order of Operations and how to ensure that your formulas calculate the way that you intend.
• • We went over all of Excel’s Function Errors and some of the ways to deal with them, as well as the potential issues of suppressing all errors.
• • Finally we walked through examples of commonly used business functions.

Review Questions

1. What functions can you find in the AutoSum Wizard

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

e. __________________________________________________

2. What are four of the Function Library groups?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

3. What’s the difference between a Function and a Formula?

a. __________________________________________________

4. How is =12+(5*2) different from =12+5*2?

a. __________________________________________________

5. What are some Functions that will be useful as you become more comfortable with Excel?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

Lesson Assignment – Lesson 6 – Using Functions & Formulas

Your assignment is to open the companion workbook and review the examples there, if you haven’t already. Once you’ve had some time to get the feel for the functions and formulas there, insert a new worksheet and start entering the functions that you identified in Question 5.

(There is a Notes section below for you to keep track of your observations):