Learning New Functions

In Excel, one of the many ways we are able to accomplish complex tasks is with the help of functions. We are going to look at some functions that can help us and the syntax of the various functions. Syntax is the structure of the formula we will use for the function. Most functions will have a syntax that looks similar to this:

= [function name]([information needed for function])

The equal sign (=) is the sign to Excel that we will use as a function. If you don’t input the equal sign, then the function will not do anything. The function name lets Excel know which function to use. Usually, the function has a shortened version of the function name that you will put in there. As you learn more functions, you will learn what you need to put in the function name section. After you put the function name, the next step is putting the parentheses (). The parentheses separate the function name from the information you will need for the function to run. The information needed for the function will differ between the different functions, but usually, this information is cell names or ranges, or sometimes they are options depending on how you want to use the function.

For example, let’s look at the sum function. The sum function’s purpose is to add numbers together. The syntax of this function is the following:

= SUM(number1, [number2],...)

The number2 is in a bracket because that means it is an optional input. The sum function adds whatever numbers are entered in the informational section. If you type =SUM into a cell in Excel and press tab then Excel understands where the function starts. Once you do so you will notice it will put the first parenthesis for you, and below, it will display a small box holding the information shown above. That is there to help you remember the syntax of the function for your convenience. It is also good to note the formula bar at the top of the sheet shows the part of the formula you have entered. If you click up there, it will display the same box that appeared when you first hit the tab on the formula’s cell.

If you click on the fx symbol next to the formula bar, it will open a window for you to put the information into with explanations of the different information you will need to put in and a general description of what the function does. Next to the text boxes, there is a button with an upward arrow symbol with an underline. Clicking on that button will allow you to select a cell or range of cells. That way, if you want to add the information in cells instead of manually inputting the number information, you can do it like that. You can also type the cell names in, or if you used a named range or named cell, you can always just type that name in. When you use a cell in a formula instead of a number, the formula will update automatically and show the new result if you change any of the information in any of the cells used in the formula. So, whenever possible, you should use cell references instead of manually typing in information.

When inputting the information for a function, you can either type/select the cells for the information in the cell, use the formula bar in a similar manner, or you can use the Fx button to open the window and put the information in there.

Let’s look at a new example for a more complicated function. Input the RANK.AVG function and press the fx key we can see three different types of values to put in. There are three different arguments for you to put in. The first one is Number, the second one is Ref, and the last one is Order. If you noticed, both Number and Ref are in bold while Order is not. That is because Order is an optional argument. If we were looking at the syntax for the formula, it would be in brackets to show how optional it is. Now, let’s break down what each of these arguments are. The Number argument is which number we want to find the rank of. The Ref argument is the range we are ranking. This range could be a list of numbers, but most likely, it will be a group of cells. The Order argument determines the order in which we rank the list. Are we ranking highest to lowest or lowest to highest? The question is answered by the Order argument. To use the Order argument, you either enter a 0 or a 1. Zero means that the highest value will be ranked first, and the lowest value will be ranked last. If you enter a 1, then the rankings will be reserved

The important part of this section of the reading is not learning these particular functions but rather being able to see how you can see the specific syntax or structure of each function and how Excel shows you can get other help. One way you can find that help is that when you click the fx button on the bottom left of the window that opens up there is a blue link that says “Help on this function”. If you click on it then it will open up the Microsoft Support page with specific help on whatever function you are trying to make. The help page will include a quick summary of the function, a video showing an example of it, an explanation of the syntax, best practices with the function, and frequently asked questions. If all of those don’t help solve your concern, there are also links to contacting an Excel expert and a link to a community answer page. So, in addition to this video, you can also use the video about how to use search engines to help you as you learn new functions.

This content is provided to you freely by BYU-I Books.

Access it online or download it at https://books.byui.edu/bus_115_business_app/using_functions.