Introduction to Functions

The Structure of a Function   Introduction A function is a small assignment that is performed to produce a result that can be reliably used without caring as to how the function works or how it was created. It is like when you pick up a TV remote control and press a button to change the channel. You don’t care how the remote control works and you don’t spend any time finding out why the channel changed. As in real world where we use various functions on cars, radio stations, food eating, etc, in the computer world, various functions are made available so you can simply use them to do your job. As a spreadsheet application, Microsoft Excel is equipped with various functions that can solve different types of calculations. In order to use a function more effectively, you should first know whether it is available and what you need to do to make it work. A function involves four main issues: The name of the function: Obviously, in order to use a function, you must know its name. Although the functions are not case-sensitive, it is a good idea to always write them in uppercase. The purpose of the function: what the function used for. Different functions are meant for different purposes. For example, when you press the power button on a TV remote control, the TV gets turned ON or OFF depending on whether it was already ON or OFF. Therefore, the purpose of the power button (that is, its function) is to turn the TV ON or OFF and vice versa What the function needs in order to carry its assignment: this need is called argument (or arguments). A function can use more than one argument. For example, you use one key to turn on the car. Another function may need more than one argument. The arguments can be the same kind. For example, some remote controls use two similar batteries. The arguments may also be different kind. On a function that takes one argument, the argument may be required. In this case, you must provide it. If you don't, the function will not work (the result would be an error). In other circumstances, an argument may not be required. In this case, if you don’t provide the argument, the function would use its own value, called a default argument. If a function takes more than one argument, all arguments may be required. In this case, if you fail to provide all of them, the function would not work. Another function that takes more than one argument may not require all of them. There are even cases when a function takes many arguments but none of them is required. When an argument is not required, you don’t have to supply it. If you don’t, then the function would use a default value for that particular function. The person who creates a function also decides on the number of its arguments, if the argument(s) is/are required and, if the function takes more than one argument, which ones are required, whether all of them are required or none of them is required. The result of the function: after using a function, you need its result so you can use it for its intended purpose. Various functions produce various results but each function is intended for a specific purpose. For example, one function may take two words as the first and the last name. The function may then produce a full name. Another function may take one number, multiply it by its own number, and produce a new number. Yet another function may take a sentence and a number then produce another value. Using a Function To us a function in your spreadsheet, you can display its result in a cell. Because a cell is just a box ready to display anything, it is your job to get the result ready to display. Fortunately, and as we have seen in the past, the cell is equipped to interpret values that are provided to it. If you are displaying the result of a function in a cell, the use and implementation of a function always starts with an equal symbol "=" followed by the name of the function. If you know the name of a function and its arguments, to use it, you can type = in a cell, followed by the name of the function, followed by an opening parenthesis. Microsoft Excel will know that you are trying to use a function and will offer to help. The functions that ship with Microsoft Excel can be divided in various categories.

 

Fundamental Functions

 

Introduction

Before getting into functions, let's experiment with Microsoft Excel's use and recognition of mathematical operations. Microsoft Excel is aware of such algebra operations as the addition (+), the subtraction (-), the multiplication (*), and the division (/). These operations can be applied to numbers typed in a cell when performing the operation, they can be gotten from another cell.

The AutoSum

The SUM function is the most basic and one of the most popular functions used in Microsoft Excel. It is used to get the addition of various numbers or the contents of various cells. The result can be displayed in another cell or used in an expression. The Standard toolbar is equipped with a button called the AutoSum. There are two most primary ways of using the AutoSum. You can click an empty contiguous cell, and then click the AutoSum button . Before performing the SUM function, the computer will ask whether it found the right cells that you want to get the sum of. If the computer found the right cells, you can press Enter; otherwise use your mouse or your keyboard to select the cells you want to consider. You can also select the cells involved in a sum plus an empty cell that will be used to display the result, and then click the AUTOSUM button.

Practical Learning: Using AutoSum

Open the Red Oak High School1 workbook you created in Lesson7 In Sheet1, press Ctrl + A and Ctrl + C Click Sheet3. Press Ctrl + Home and press Enter Expand it with the following new values in columns H, I, and J In cell B11, type TOTAL Click cell C11 to give it focus On the Standard toolbar, click AutoSum . Notice that Microsoft Excel selects the cells on the upper region of the selected cells; their blinking border is the application's way of asking whether these are the cells you want to get the sum of   Press Enter Select cells D3:D10 On the Standard toolbar, click AutoSum . Notice that Microsoft Excel displays the sum result directly. This is because you had selected the cells involved in the sum plus an empty adjacent cell that will display the result In cell G11, type =SUM( Select cells G3:G10 and press Enter In cell H11, type =SUM( Click cell H10 and type , Click cell H3 and type , Click cell H8 and type , Click cell H5 and type , Click cell H9 and type , Click cell H4 and type , Click cell H7 and type , Click cell H6 and press Enter. Notice that you don't have to follow an order of cells, just tell Microsoft Excel which cells are involved in the sum Click cell I11 to give it focus On the Standard toolbar, click the Paste Function button From the Paste Function dialog box, in the Function Category list box, select Math & Trig In the Function Name list box, click SUM   Click OK. Notice a new dialog, SUM, on the screen Position your mouse on a gray area that is not a button, then click, hold your mouse down and move it to a different area until you can see cell I13 Notice that the Number 1 box already has the range of cells I3:I10   Click OK Right-click cell I11 and click Copy Right-click cell J11 and click Paste. Notice that the pasted result is not the content of cell I11, only the formula has been copied but only conform to column G Click cell J11. Position your mouse on its Fill Handle (the lower right point of the selected cell). Click and drag its Fill Handle to cell K11 Notice that once again it is the formula that was copied to the subsequent cell Save the workbook

AutoCalculate

On the Status Bar, the AutoCalculate pane allows you to get a quick result of the most used functions in Microsoft Excel.

Practical Learning: Using AutoCalculate

Select cells D5:D12 and observe the AutoCalculate section of the Status Bar Right-click the AutoCalculate section (or anywhere on the Status Bar) and click None. Notice that the AutoCalculate section displays nothing Right-click the AutoCalculate section again and click Sum   Now the AutoCalculate section displays the sum of the selected cells.

The Absolute Value

The decimal numeric system counts from minus infinity (-∞) to infinity (+∞). This means that a  number can be usually negative or positive, depending on its position from 0, which is considered as neutral. In some operations, the number considered will need to be only positive even if it is provided in a negative format. The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of –12 is 12. To get the absolute value of a number, you can use one of the ABS() function. Its syntax is: ABS(number)

The Ceiling of a Number

Consider a floating number such as 12.155. As you can see, this number is between integer 12 and integer 13 On the other hand, consider a number such as –24.06. As this number is negative, it is between –24 and –25, with –24 being greater. In arithmetic, the ceiling of a number is the closest integer that is greater or higher than the number considered. In the first case, the ceiling of 12.155 is 13 because 13 is the closest number greater than or equal to 12.155. The ceiling of –24.06 is –24. To find the ceiling of a number, you can use the CEILING function used to obtain the ceiling of a number uses the following syntax: CEILING(number, significance)   The function takes two arguments. The number argument is the one that will be considered. The significance argument is the nearest multiple significance

The Floor of a Number

Consider two floating numbers such as 128.44 and -36.72. The number 128.44 is between 128 and 129 with 128 being the lower. The number –36.72 is between –37 and –36 with –37 being the lower. The lowest but closest integer value of a number is referred to as its floor. For example, the floor of 128.44 is 128. The floor of –36.72 is –37. To get the floor of a number, you can use the FLOOR function. Its syntax is: FLOOR(number, significance) The FLOOR() function takes two arguments. The first argument argument is the number to be considered. The second argument is the nearest multiple of significance.

The Power of a Number

We saw in Lesson8 that, to raise a number to another, you could use the ^ operator. Microsoft Excel provides a function to perform the same operation. The POWER() function is used to calculate the value of one number or expression raised to the power of another number. It uses the formula xy. The syntax of the POWER function is: POWER(number, power)   This function takes two required arguments. The first argument, number, is used as the base number to be evaluated. The second argument, power, also called the exponent, will raise number to this value.

The Exponential

Microsoft Excel provides the EXP function used to calculate the exponential value of a number. Its syntax is: EXP(number) The argument, number, a double-precision value, represents the number to be evaluated. If the value of number is less than -708.395996093 (approximately), the result is reset to 0 and qualifies as underflow. If the value of the argument x is greater than 709.78222656 (approximately), the result is infinity and qualified as overflow.

The Log10

The LOG10 function calculates the base 10 logarithm of a number. The syntax of this function is: LOG10(number) The number to be evaluated is passed as the argument number. The function returns the logarithm on base 10 using the formula: y = LOG10x which is equivalent to x = 10y

The Square Root

There are two forms of calculating the square root of a (real positive) number. The SQRT function is used to calculate the square root of a double-precision number. Its syntax is: SQRT(number) This function takes one argument as a positive floating number. After the calculation, the function returns the square root of x: = SQRT(E12)

Other arithmetic functions include COMBIN, EVEN, EXP, FACT, INT, LN, LOG, LOG10, MOD, ODD, PI, RAND, ROMAN, ROUND, ROUNDDOWN, ROUNDUP, SIGN, SQRT, SUBTOTAL, SUMSQ, and TRUNC.