COMP100 Week 6 Working With Excel Functions Lecture Notes

17. April 2016 School 0

Working with Excel Functions

Introduction

Last week we focused on formulas. This week we will focus on functions. Studying formulas last week, gives us an excellent foundation for understanding what functions are and how they work. In many ways, a function is like a formula. Remember that a formula is an equation in a cell that calculates a result in that cell. A function does the same thing! It executes an equation and returns a result to the cell it is in. But it is much simpler to set up and use than a formula. Why is that true? Because the equation has already been written and is hidden in the function. We can’t see it! That is why, a function is also called a “black box.” And we don’t care what the logic is because we know that each function’s logic is correct and will give us the results we want. Who wrote the logic in functions in Excel? It was written by software developers from Microsoft. Now, knowing that the logic is correct, you then only need to know which function to use for the result you want and then you need to know how to set it up with the proper syntax.




Every function has a name and syntax. The name of a function is a short word or abbreviation that describes what the function does. A short word or abbreviation for a function is also referred to as a mnemonic (pronounced “nemonik”). For instance, SUM is the mnemonic for one of the Excel functions. The SUM function adds values that are in a range of cells. Another function name is SQRT. SQRT stands for “Square Root.” It returns the square root of a value. Every function has a code structure that must be adhered to. This structure is called syntax. Syntax is like using proper grammar in the English language. It is the structure of a coherent sentence. You are viewing syntax right here, right now! When you build a sentence, you must follow the rules of proper grammatical structure. If you don’t, then the sentence will not make any sense. The same is true for a function. Remember that you are not writing the logic for the function but are instead communicating with the function by providing it with the syntax or in other words, structure that it expects. The syntax requires that you enter parts of a function code in a specific order with proper punctuation. It specifies exactly where the parentheses, commas, and other punctuation must appear for the function to work correctly. When calculating for a result, Excel uses arguments, which are cell references, text, or numbers.

There are hundreds of functions within the 10 categories in Excel. Those categories of functions are Date and Time, Financial, Database, Information, Lookup, Logical, Text and Data, Math, Engineering, and Statistical. Excel also has advanced functions, like SQL.REQUEST and EUROCONVERT, which are utilized by an External function (another type of function). You may learn about External functions in another course or some other time during your career, but we will not get into them in detail in this lecture.

In this week’s lab, you will be using the SUM, AVERAGE, MAX, MIN, COUNT, PMT, VLOOKUP, and IF functions. Some of these are very simple to use and are quite intuitive, such as SUM and AVERAGE. Functions such as PMT, VLOOKUP, and IF will take some explaining which the textbook does very well in the hands-on exercises. We will also cover them in the discussions as well.

Entering the SUM Function

Let’s take a look at one of the simplest and most popular functions: the SUM function. The name itself should give you a hint on what it is used for. It is used to sum or, in other words, total two or more cell values. If you have a few numbers to add, you could use a basic formula, like “=B4+B5+B6+B7” to complete your calculation. So why have a SUM function? Well, if you need to add 200 cells in column B, you wouldn’t want to type out 200 cell references in the formula. Instead the function syntax provides you with specifying a range of cells. So instead of typing out 200 cell references, you would specify a beginning cell reference and an ending cell reference. Much easier to do that wouldn’t you say? Also, it is simpler to type in. You’ll save yourself a lot of time. To do this, simply choose the cell in which you’d like the total to appear, and type in “=SUM(B1:B200).” B1:B200 would be the range of cells that the function will sum.

Searching for Functions Related to Loan Payments

Under the Insert Function Dialog Box, there is a list of the different functions you can use in Excel. For example, to determine the monthly payment on a loan, you can use the PMT function. You can see some of the other functions that have already been mentioned like LOOKUP and IF.



Function Argument Dialog Box for the PMT

So let’s say that we decide to use the PMT function. First you click on the PMT entry in the Insert Function Dialog Box. Then the Function Arguments box will appear for the PMT function. The arguments for the PMT function appear in the order in which they should be entered. This is a nice feature when using the Insert Function, as it displays the arguments in the correct order. For each argument, you will see a short explanation on how to set up that argument. Some arguments are mandatory and some are optional. Once you have filled in the arguments and clicked on the OK button, the function will display in the Formula Bar in its proper syntax. Of course you can code the PMT function syntax directly into the Formula Bar but if you are not familiar with the PMT function, then the Functions Arguments Dialog Box would be the easier way to set the syntax up.


Leave a Reply

Your email address will not be published. Required fields are marked *