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 • Lookup & Reference • Date & Time • Math & Trig • Statistical • Financial • Database (Ch 9)

5. Finding and Using Functions • Insert > Functions from menu • fx icon in Standard toolbar • Insert Function menu has descriptions of each function with Help options

6. Function Arguments • Almost every Function has at least one argument, or parameter • The Function Arguments window describes what each argument is and offers more details with the Help option

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

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

9. Figure 4.4(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)

10. Logical Functions • TRUE and FALSE • IF and NOT • AND and OR

11. Figure 4.7 • 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)

12. Figure 4.8 • 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)

13. Figure 4.9(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

14. Figure 4.9(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

15. 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

16. Figure 4.11 • 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)

17. Figure 4.12 • 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)

18. Figure 4.13 • 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

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

20. Figure 4.15 • 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)

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

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

23. 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

24. 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)

25. VLOOKUP and HLOOKUP (cont) • 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

26. Figure 4.18 • =VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)

27. Figure 4.19 • =HLOOKUP(lookup_value, table_array, row_index_number, range_lookup)

28. 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

29. Figure 4.20 • Searching a column for closest matches

30. Figure 4.21 • Searching a row for an exact match

31. Figure 4.23 • 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: Use the INDEX function to compute the distance between US cities.

32. Figure 4.26 • 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.

33. 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, 1990 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.

34. 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

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

36. 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

37. Figure 4.31 • =NETWORKDAYS(start_date, end_date, holidays)

38. Figure 4.32 • =DAYS360(start_date, end_date, method)

39. Figure 4.33 • =YEARFRAC(start_date, end_date, basis)

40. Four More Functions • 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

41. Four More Functions (cont) • The MONTH function determines to which month your date belongs. • The months are enumerated with January as 1 through December as 12. • 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.

42. Figure 4.35 • =WEEKDAY(date, method) or =WEEKDAY(cell_name, method)

43. Figure 4.36 • =MONTH(date) or =MONTH(cell_name)

44. Figure 4.37 • =HOUR(time) or =HOUR(cell_name) • =MINUTE(time) or =MINUTE(cell_name)

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

46. SUMPRODUCT • The SUMPRODUCT function takes several arrays and finds the sum of the product of each element in these arrays • =SUMPRODUCT(array1, array2, …) • This is equivalent to taking the product of several rows of values, storing the results in a column, and then taking the sum of that column of values

47. Figure 4.38

48. MMULT • The MMULT function is used to multiply two matrices, or ranges, of values. • =MMULT(array1, array2) • =MMULT(range_name, range_name) • To be able to multiply two matrices, the number of columns of one matrix must equal the number of rows of the other matrix.

49. Figure 4.40 • First, highlight a range of cells with the dimension of number of rows of the first matrix by number of columns of the second matrix. • Then enter the MMULT equation and press SHIFT+CTL+ENTER.

50. Statistical Functions • We describe in detail such statistical functions as MEAN and STDEV in Chapter 7, as well as such distribution functions as NORMDIST, BETADIST, CHIDIST, and EXPONDIST. • In Chapter 10, we will discuss the COUNT functions, COUNTIF and SUMIF,with databases functions.