Chapter 4: Functions and Formulas

1. Spreadsheet-Based Decision Support Systems Chapter 4: Functions and Formulas Prof. Name name@email.com Position (123) 456-7890 University Name

2. Overview • 4.1 Introduction • 4.2 Formulas and Function Categories • 4.3 Logical and Information Functions • 4.4 Text and Lookup & Reference Functions • 4.5 Date & Time Functions • 4.6 Mathematical and Trigonometry Functions • 4.7 Statistical and Financial Functions • 4.8 Conditional Formatting Formulas • 4.9 Auditing • 4.10 Summary

3. Introduction • Various function names, arguments, and examples • Excel’s function dialog boxes • Formula Is option in conditional formatting • Auditing formulas and functions

4. Formulas and Function Categories • Formulas • Simple values • Basic operators • Naming and referencing • Functions • Function Categories • Logical • Information • Text and • Lookup & Reference • Date & Time • Math & Trig • Statistical • Financial

5. Basic Operators • Basic operators perform simple calculator functions such as addition, subtraction, multiplication, and division with numbers in cells. • These mathematical functions used in a cell follow the order of operations. • To do this, we type an “=” followed by the equation into the cell.

6. Finding and Using Functions • Function Library group in the Formulas tab of the Ribbon lists a number of function categories: • Financial, Logical, Text, Date and Time, Lookup & Reference, and Mathematics and Trigonometry • A list of functions are available for use within each category by selecting the corresponding command from the Function Library group.

7. Finding and Using Functions • To display the Insert Function dialog box, we click on the Insert Function fxicon located both, in the Function Library group, and in the Formula Bar.

8. Function Arguments • Once we select a function, the Function Arguments dialog box appears. • Almost every Function has at least one argument, or parameter. • The Help on this Function command in the bottom-left corner of these dialog boxes gives a detailed explanation of the function as well as an example of how to use it.

9. Finding and Using Functions (cont’d) • The Formulas tab > Function Library group > Recently Used command displays approximately the last ten functions used. • The Formulas tab > Function Library group > More Functions command lists additional function categories such as, Statistical, Engineering, Cube, Information and Compatibility. • Formula Autocomplete is a useful feature that displays a menu of all functions that begin with the specified letters.

10. Figure 4.7(a) • The SUM function sums the values in any given range of values • =SUM(number1, number2) or =SUM(range name)

11. Figure 4.7(b) • The AVERAGE function finds the average of a given range • =AVERAGE(number1, number2) or =AVERAGE(range name)

12. Figure 4.7(c) • The MIN and MAX functions find the minimum or maximum value in a given range of values • =MIN(number1, number2) or =MIN(range name) • =MAX(number1, number2) or =MAX(range name)

13. Logical Functions • TRUE and FALSE • NOT • IF and IFERROR • AND and OR

14. Figures 4.9 and 4.10 • The TRUE and FALSE functions simply display the words TRUE and FALSE respectively (no parameters) • =TRUE or =FALSE • The NOT function is used to display the opposite of any of the results of the other Logical functions • =NOT(cell_name)

15. Figure 4.11 • The IF function uses a specified condition to determine whether your data is true or false, and then returns a user-specified result in each case. • =IF(logical_test, value_if_true, value_if_false) • The IFERROR function returns a user-specified value if the formula gives an error, and the value of the formula itself otherwise. • =IFERROR(value, value_if_error)

16. Figure 4.12(a) • The AND function evaluates a list of conditions as True or False • =AND(condition1, condition2, …) • All of the conditions must be true in order for TRUE to be displayed. • If any of the conditions are violated, FALSE will be returned

17. Figure 4.12(b) • The OR function also evaluates a list of conditions • =OR(condition1, condition2, …) • Only one of the conditions needs to be true for TRUE to be the result • All of the conditions would have to be violated in order to return FALSE

18. Information Functions • There are several different Information functions • All of these functions give some basic descriptive information about any given data • One group of these functions we call the IsFunctions

19. Figure 4.14 • The ISEVEN and ISODD functions evaluate whether or not a number in a cell is an even or odd number, respectively. • =ISEVEN(cell_name) or =ISEVEN(number) • =ISODD(cell_name) or =ISODD(number)

20. Figure 4.15 • The ISTEXT and ISNUMBER functions return TRUE or FALSE if a cell value is text or not, or a number or not, respectively. • =ISTEXT(cell_name) or =ISTEXT(value) • =ISNUMBER(cell_name) or =ISNUMBER(value)

21. Figure 4.16 • The TYPE function evaluates the data type of a value • =TYPE(cell_name) or =TYPE(value) • a data type is a descriptive category of the different types of values possible in Excel • Excel has designated a particular number to reference the categories of each data type. • 1 = numerical • 2 = text • 4 = logical value

22. Text Functions • Text functions manipulate text values or analyze their characteristics • There are several Text Functions. We will discuss: • UPPER and LOWER • CONCATENATE • SUBSTITUTE.

23. Figure 4.18 • The UPPER and LOWER functions convert a cell, or range of cells, with text values into all uppercase or all lowercase text, respectively • =UPPER(range_name) or =LOWER(range_name)

24. Figure 4.19 • The CONCATENATE function joins fragments of a phrase or sentence together by combining text values of multiple cell • =CONCATENATE(cell1, cell2, …)

25. Figure 4.20 • The SUBSTITUTE function takes a cell with text and exchanges old text for new text. • =SUBSTITUTE(cell_name, old_text, new_text, instance)

26. Lookup & Reference Functions • Lookup & Reference functions search for information within a given table of data and perform some actions on that data • There are several of these functions; for now, we describe: • VLOOKUP and HLOOKUP • MATCH • INDEX • OFFSET

27. VLOOKUP and HLOOKUP • The VLOOKUP and HLOOKUP functions are helpful when searching for data in the spreadsheet • VLOOKUP searches for a value in the left-most column of a table, marks the row that contains that value, and then returns a value from that row for a specified column • =VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)

28. VLOOKUP and HLOOKUP (cont’d) • HLOOKUP searches for a value in the top row of a table, marks the column that contains that value, and then returns a value from that column for a specified row • =HLOOKUP(lookup_value, table_array, row_index_number, range_lookup) • The range_lookup parameter measures the exactness for searching for the first parameter value. • True = find the closest match (default) • False = find an exact match

29. Figure 4.21 • =VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)

30. Figure 4.22 • =HLOOKUP(lookup_value, table_array, row_index_number, range_lookup)

31. MATCH • The MATCH function searches a table of data and returns the location of a desired value. • =MATCH(lookup_value, table_array, match_type) • The match_typeparameter, can be 0, 1, or –1. • 0 = the location of the first value it finds that is equal to the value for which we are searching (default) • 1 = the location of the largest value that is less than or equal to our specified value (given that the data is in ascending order) • –1 = the location of the smallest value that is greater than or equal to our value (given that the data is in descending order

32. Figure 4.23 • Searching a column for closest matches

33. Figure 4.24 • Searching a row for an exact match

34. Figure 4.25 • The INDEX function, like the MATCH function, allows us to find an entry in a specified row and column of a range of cells. • =INDEX(range or range_name, row_number, column_number) • Example: The table below stores the distances between ten US cities.

35. Figure 4.26 • Use the INDEX function to compute the distance between US cities.

36. Figure 4.27 • We can also use the INDEX function to refer to an entire row or an entire column.

37. Figures 4.28 and 4.29 • The OFFSET function references a cell that is a given number of rows and columns from a specified cell, or range of cells. • =OFFSET(reference_cell, rows_to_move, columns_to_move, [height], [width]) • Use a table of numbers to demonstrate the use of the OFFSET function. • Name the cell C2 the “RefCell” since we will reference this cell most often.

38. Figure 4.30 • The OFFSET function can also be useful in combination with other functions. • To find the sum of the values in the last column of the table, we use both the SUM and OFFSET functions.

39. Date & Time Functions • Excel’s system for calculating dates and times uses a serial number to enumerate all dates and times • For dates, this number considers January 1, 1900 to be an initial starting point, which it sets to zero, and then counts each day thereafter as one unit • For time, the initial starting point is at zero hours, zero minutes, and zero seconds counting toward the current time on a 24-hour scale. (It is reset to zero at midnight of each day.) • It is by using this numerical system that we are able to perform the functions in this category.

40. Date & Time Functions (cont’d) • There are several functions in this category; we describe: • TODAY and NOW • NETWORKDAYS, DAYS360, and YEARFRAC • WEEKDAY and MONTH • HOUR and MINUTE

41. Two Functions • The TODAY and NOW functions display the current date and time, respectively (there are no parameters for these functions) • =TODAY() • =NOW()

42. Three More Functions • NETWORKDAYS finds the number of workdays between two dates • DAYS360 finds the total number of days between two dates • YEARFRAC finds the fraction of a year between two dates • Let’s consider an example of a company that receives shipments of office supplies each month.

43. Figure 4.34 • =NETWORKDAYS(start_date, end_date, holidays)

44. Figure 4.35 • =DAYS360(start_date, end_date, method)

45. Figure 4.36 • =YEARFRAC(start_date, end_date, basis)

46. Four More Functions • The MONTH function determines to which month your date belongs. • The months are enumerated with January as 1 through December as 12. • The WEEKDAY function determines to which day of the week your date refers. There are three possible numbering methods: • 1 = Sunday as day 1 and Saturday as day 7 (default) • 2 = Monday as day 1 and Sunday as day 7 • 3 = Monday as day 0 and Sunday as 6 • The HOUR function takes the time and returns the number of the hour to which it belongs using a numbering system from 12:00 AM as 0 to 11:00 PM as 23. • The MINUTE function takes the time and returns a minute number from 0 to 59.

47. Figure 4.38 • =WEEKDAY(date, method) or =WEEKDAY(cell_name, method)

48. Figure 4.39 • =MONTH(date) or =MONTH(cell_name)

49. Figure 4.40 • =HOUR(time) or =HOUR(cell_name) • =MINUTE(time) or =MINUTE(cell_name)

50. Mathematical and Trigonometry Functions • Already used: • SUM • AVERAGE • MIN • MAX • We will now describe: • ABS • PRODUCT • SUMPRODUCT • MMULT • SQRT • PI • SIN, COS, and TAN