Formulas and Functions (I) This unit is one of the most important in the course. To get the basics of Excel you must understand and use the contents of this unit. A worksheet is just a data base that we use with a series of formulas in order to avoid recalculations for each change introduced. That is why this unit is essential to follow the course and for the proper use of Excel. We are going to go deeper into the use of functions , already defined by Excel2003, to facilitate the creation of worksheets. We are going to to study the syntax of functions and how to use the functions assistant, a very useful tool when we are still unfamiliar with the existing functions and their syntax. Introducing Formulas and Functions A function is a formula predifined by Excel2003 (or by the user) that operates with one or more values and returns a result that will appear directly in the cell or will be used to calculate the formula it contains. The syntax of any function is: name_function(argument1;argument2;...;argumentN) Functions follow these rules: - if the function is at the beginning of a formula, it must begin with the sign =. - Arguments and entry values are always in brackets. Do not leave any spaces after or before each bracket sign. - Arguments can be constant values (figure or text), formulas or functions. - Arguments should be separated by semicolons ;. Example: =SUM(A1:C8) This is a SUM() function that returns as result the addition of its arguments. The operator ":" identifies a cell range like this A1:C8 and indicates all the cells included between cell A1 and cell C8, so the function will be the same as: =A1+A2+A3+A4+A5+A6+A7+A8+B1+B2+B3+B4+B5+B6+B7+B8+C1+C2+C3+C4+C5+C6+C7+C8 In this example we can appreciate the advantage in using functions. Formulas may contain more than one function and functions may appear nested inside a formula. Example: =SUM(A1:B4)/SUM(C1:D4) There are many types of functions, according to the type of calculation or operation they perform. So functions can be: mathematical, trigonometrical, estatistic, financial, text functions, date and time functions, data base functions, search and reference functions and information functions. To introduce a formula you must type it in any cell, in the same way you introduce any text, preceded always by the sign =. If you wish further informations about most frequently used operators and the priorities of operators visit our basic . Inserting a function with the assitant A function, as any other piece of data, may be typed straight into the cell if we know its syntax, but Excel2003 offers the help of an assistant to use functions. With the assistant it will be easier for us to use functions. To introduce a function in a cell: Go to the cell where you wish to introduce it, unfold the menu Insert. Choose option Function... Or click on the button bar. on the formulas The dialoge box Insert function will appear on the right. With Excel2003 we can search the function we need typing a brief description of the function we need in the text box Search for a function and, then, click on the button . In this way, we don't have to know all the functions incorporated in Excel because the list shown in the box Select a function: will contain functions related to the description typed. To prenvent the list of functions being too long, we can previously select a category in the combine box Or select a category:, then in the list box will only appear functions from the chosen category and the list will be shortened. If you are not sure about which category you want, choose All. In the list box Select a function: you will have to choose the function you wish clicking on it. Notice that as we select a function, in the lower part the different arguments will appear together with a brief description of the function. The link Help on this function is also available to obtain a fuller description of a function. Finally, click on the button OK. This unit continues in the following page... Formulas and Functions (II) Just below the formulas bar the dialoge box Function Arguments will appear. It will ask you for the function arguments. The box will vary according to the function chosen. In this case we have chosen the function SUM (). In Number 1 text box you must indicate the first argument , that will generally be a cell or a range of cells like A1:B4 . To do that, you should click on the button so the box gets smaller and you can see all the spreadsheet. After that, select the range of cells or the cell you wish as your first argument (to select a range of cells click with the left button of the mouse on the first cell of the range and without letting go drag to the last cell of the range) and press the key ENTER to return to the dialoge box. In the box Number 2 you must indicate your second argument, of course, only when there is one. If you introduce a second argument, another box will appear for the third argument, and so on. Once you have introduced all the arguments, click on the button OK. If for whatever reason we insert a row in the middle of a function range, Excel will expand automatically the range Including the cell value in the range. For example: If you have in cell A5 the function =SUM(A1:A4) and you insert a row in position 3, the formula will expand automatically changing to =SUM(A1:A5) . In the Tool Bar there is the Autosum button function SUM faster. . With it you can perform the With this button we also have access to other functions, using the arrow on the right of the button. When you click on the arrow the dropdwon list on the right will appear: And you will be able to use a function other than Sum, for instance Average (it calculates the average), Count (it counts values), Max (it obtains the maximum value) or Min (it obtains the minimum value). Besides, you will be able to have access to the dialoge of functions with More Functions... If you want further information about references and names visit our basic Using Expressions as functions arguments . With Excel we can have expressions as functions arguments, for example the sum of two cells (A1+A3). The order of performance of the function would be: first, resolve the expressions, then perform the function on the expressions result. If we have, for instance, the function =SUM((A1+A3);(A2-A4)) where: A1 equals 1 A2 equals 5 A3 equals 2 A4 equals 3 Excel will resolve first the expressions (A1+A3) and (A2-A4) therefore we will obtain the values 3 and 2 respectively, after that it will perform the sum obtaining 5 as result. Using functions as functions arguments With Excel a function can become an argument of another function. In that way we are able to perform really complex operations in a single cell. For example: =MAX(SUM(A1:A4);B3) , this formula consists of a two function combination, the sum and the maximum value. Excel will perform first the sum SUM(A1:A4) and then will calculate the maximum value between the result of the sum and the cell B3. Date and Time Funtions In this section we are going to see the functions that are applied to dates and times. With these we will conclude our instructions in the functions offered by Excel. We are going to see that in several funtions the argument that is passed to them or the value they return is a "numeric value*". Excel calls numeric value to the number of days gone by since 0 of January 1900 to the date introdouced. That is to say, it takes as the initial date of the system 0/01/1900 and it starts counting from there. In the functions that have mumeric_value_ as argument, you will be able to type a figure or the reference of a cell that contains a date. See in detail Function NOW() YEAR(serial_number) DAY(serial_number) DAYS360(start_date;end_date;method) WEEKDAY(serial_number;return_type) DATE(year,month,day) DATEVALUE(date_text) HOUR(serial_number) TIMEVALUE(time_text) TODAY() MONTH(serial_number) MINUTE(serial_number) Description Returns current date and time Returns the year in year format Returns the day of the month Calculates the number of days btween two dates Returns a number between 1 and 7 Returns the date in date format Returns de date in date format Returns in figures from 0 to 23 Converts a time in text into figures Returns current date Returns the number of the month in the range 1(January) to 12 (December) Returns the minute in the range 0 to 59 TIME(hour;minute;second) SECOND(serial_number) Converts hours, minutes and seconds given in figures Returns the second in the range 0 to 59 You can practise some of these functions in the Exercises about dates and time functions . Formulas and Functions (IV) Text functions Spreadsheets are designed to be used with figures. But Excel also has a number of specific functions for the manipualtion of texts. These are all the text functions offered by Excel. Function CHAR(number) CODE(text) CONCATENATE(text1;text2,...;textN) FIXED(number;decimals;no_commas) RIGHT(text;num_chars) SEARCH(find_text;within_text;start_num) TRIM(text) MID(text;start_num;num_chars) FIND(find_text;within_text;start_num) EXACT(text1;text2) LEFT(text;num_chars) LEN(text) CLEAN(text) UPPER(text) LOWER(text) Description Returns the character specified by the number Returns the ASCII code of the first character of the text Returns a chain of characters with the union Rounds (up or down*) a figure passed as parametre Returns the number of characters specified Returns the initial position of the text searched Removes all spaces between words except for single spaces between words Returns the characters indicated in a chain Finds a chain inside a text Returns a logical value (true/false). Check whether two texts are strictly the same Returns the number of characters specified Returns the length of the text Cleans the text, gets rid of characters that can't be printed Converts into capital letters Converts into small letters (lower case) See Details DOLLAR(number;decimals) PROPER(text) REPLACE(old_text;start_num;num_chars;new_text) REPT(text;number_times) SUBSTITUTE(text;old_text;new_text;instance_num) T(value) TEXT(value;format_text) BAHTTEXT(number) VALUE(text) Converts into currency Converts into capital the first letter of a text Replaces part of a text chain for another Repeats the text Substitutes a text for another Checks up that the value is text Converts a value in text Converts a number in Thai text (Baht) Converts a text in a number You can practise some of the above functions in Exercises about text functions .