CIS300 Exam 3 Review

1
CIS300 Exam 3 Review
2
CIS 300 Exam 3 Content
MIS Chapter 6,7 Cumulative concepts, features
and functions, plus new functions COUNTIFS,
SUMIFS, AVERAGEIFS (Separate ppt on
REACH.louisville.edu) All assigned course
Homework and Lab Assignments
3
CIS300 Cumulative Microsoft? Excel ? Functions

• Text Functions
• FIND
• LEFT
• LEN
• RIGHT
• MID
• UPPER
• LOWER
• PROPER
• CONCATENATE
• TRIM
• CLEAN
• REPLACE
• ISTEXT

• Date Time Functions
• TODAY
• NOW
• WEEKDAY
• YEARFRAC
• DATE
• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• DATEDIF

• Error Functions
• ISERROR
• ISNA

• Lookup Functions
• VLOOKUP
• HLOOKUP

4
The TODAY function
Syntax TODAY() Arguments None
5
The TODAY function

• Description
• Returns the serial number of the current date.
• Remarks
• If the cell format was General before the
  function was entered, Excel changes the cell
  format to Date.
• If you want to view the serial number, you must
  change the cell format to General or Number.
• The TODAY function is useful when you need to
  have the current date displayed on a worksheet,
  regardless of when you open the workbook.
• The TODAY function is dependent on your
  computers system clock being correct.
• Errors
• None

6
The TODAY function
TODAY()
7
The NOW function
Syntax NOW() Arguments None
8
The NOW function

• Description
• Returns the serial number of the current date and
  time.
• Remarks
• If the cell format was General before the
  function was entered, Excel changes the cell
  format to the same date and time format that is
  specified in the regional date and time settings
  in Control Panel.
• The NOW function is useful when you need to
  display the current date and time on a worksheet
  or calculate a value based on the current date
  and time, and have that value updated each time
  you open the worksheet.
• Numbers to the right of the decimal point in the
  serial number represent the time numbers to the
  left represent the date.
• The results of the NOW function change only when
  the worksheet is calculated or when a macro that
  contains the function is run. It is not updated
  continuously.
• Errors
• None

9
The NOW function
NOW()
10
The NOW function
NOW()
11
The WEEKDAYfunction

• Syntax
• WEEKDAY(serial_number,return_type)
• Arguments
• serial_number Required
• A sequential number that represents the date of
  the day you are trying to find. Dates should be
  entered by using the DATE function, or as results
  of other formulas or functions. For example, use
  DATE(2008,5,23) for the 23rd day of May, 2008.
  Problems can occur if dates are entered as text.
• return_type_ Optional
• A number that determines the type of return
  value.

12
The WEEKDAYfunction
RETURN_TYPE NUMBER RETURNED
1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2 Numbers 1 (Monday) through 7 (Sunday).
3 Numbers 0 (Monday) through 6 (Sunday).
11 Numbers 1 (Monday) through 7 (Sunday).
12 Numbers 1 (Tuesday) through 7 (Monday).
13 Numbers 1 (Wednesday) through 7 (Tuesday).
14 Numbers 1 (Thursday) through 7 (Wednesday).
15 Numbers 1 (Friday) through 7 (Thursday).
16 Numbers 1 (Saturday) through 7 (Friday).
17 Numbers 1 (Sunday) through 7 (Saturday).
13
The WEEKDAYfunction

• Example

Formula Description (Result)
WEEKDAY(A2) Day of the week, with numbers 1 (Sunday) through 7 (Saturday) (5)
WEEKDAY(A2, 2) Day of the week, with numbers 1 (Monday) through 7 (Sunday) (4)
WEEKDAY(A2, 3) Day of the week, with numbers 0 (Monday) through 6 (Sunday) (3
WEEKDAY(A2)
5
14
The YEARFRAC function

• Syntax
• YEARFRAC(start_date,end_date,basis)
• Arguments
• start_date Required
• A date that represents the start date.
• end_date Required
• A date that represents the end date.
• basis Optional
• The type of day count basis to use.

15
The YEARFRAC function

• Description
• Calculates the fraction of the year represented
  by the number of whole days between two dates
  (the start_date and the end_date).
• Remarks
• Use the YEARFRAC worksheet function to identify
  the proportion of a whole year's benefits or
  obligations to assign to a specific term.
• Dates should be entered by using the DATE
  function, or as results of other formulas or
  functions.
• All arguments are truncated to integers.
• Errors
• VALUE If start_date or end_date are not valid
  dates
• NUM! If basis lt 0
• NUM! If basis gt 4

16
The YEARFRAC function
What is the fraction of the year between the two
dates?
YEARFRAC(A2,A3,A4)
17
The YEARFRAC function
What is the fraction of the year between the two
dates?
YEARFRAC(A2,A3,A4) 0.583333333
18
The DATE function

• Syntax
• DATE(year,month,day)
• Arguments
• year Required
• The year of the date you want to output.
• month Required
• The month of the date you want to output.
• day Required
• The day of the date you want to output.

19
The DATE function
What is the date using the information give below?
DATE (B1,B2,B3) 2/20/2012
20
The YEAR function

• Syntax
• YEAR(serial_number)
• Arguments
• year Required
• The date you wish to find the year of.

21
The MONTH function

• Syntax
• MONTH(serial_number)
• Arguments
• serial_number Required
• The date you wish to find the month of.

22
The DAY function

• Syntax
• DAY(serial_number)
• Arguments
• year Required
• The date you wish to find the day of.

23
The YEAR, MONTH, and DAY function
What is the year, month, and day of the date
below?
CELL A1 - 5/29/2035
YEAR(A1) 2035
MONTH(A1) 5
DAY(A1) 29
24
The HOUR function

• Syntax
• HOUR(serial_number)
• Arguments
• Serial_number Required
• the time value to extract the hour from. It may
  be expressed as a string value, a decimal number,
  or the result of a formula.

25
The HOUR function
What is the number that will be displayed in A7?
HOUR(A6) 12
26
The MINUTE function

• Syntax
• MINUTE(serial_number)
• Arguments
• Serial_number Required
• the time value to extract the hour from. It may
  be expressed as a string value, a decimal number,
  or the result of a formula.

27
The SECOND function

• Syntax
• SECOND(serial_number)
• Arguments
• Serial_number Required
• the time value to extract the hour from. It may
  be expressed as a string value, a decimal number,
  or the result of a formula.

28
The DATEDIF function

• Syntax
• DATEDIF(startdate,enddate,interval)
• Arguments
• startdate Required
• A date that represents the start date.
• enddate Required
• A date that represents the end date.
• interval Required
• The type of day count basis to use.

29
The DATEDIF function

• Syntax
• DATEDIF(startdate,enddate,interval)
• Arguments
• interval Required
• The type of day count basis to use.

30
The DATEDIF function

• Description
• Computes the difference between two dates in a
  variety of different intervals.
• Remarks
• If you have the interval in another cell
  referenced by the formula, that cell should not
  have quotes around the interval string.
• When calculating date intervals, DATEDIF uses the
  year of startdate, not enddate when calculating
  the yd, ym and md intervals
• Errors
• VALUE If start_date or end_date are not valid
  dates
• NUM! If start date is not less than or equal
  to the end date

31
The DATEDIF function
What is the difference in days between the two
dates?
DATEDIF(A2,A3,d)
32
The DATEDIF function
What is the difference in days between the two
dates?
DATEDIF(A2,A3,d) 210
33
Microsoft? Excel? Text Functions
FIND FIND(find_text,within_text,start_num) LE
FT LEFT(text,num_chars) LEN LEN(text) RIGH
T RIGHT(text,num_chars) UPPER UPPER(text)
LOWER LOWER(text) PROPER PROPER(text) CONCAT
ENATE ((including ) CONCATENATE(text1,
text2, ...)
34
The FIND function

• Syntax
• FIND(find_text,within_text,start_num)
• Arguments
• find_text Required
• The text you want to find.
• within_text Required
• The text string containing the text you want to
  find.
• start_num Optional
• Specifies the character at which to start the
  search.

35
The FIND function

• Description
• Locates one text string within a second text
  string, and returns the number of the starting
  position of the first text string from the first
  character of the second text string
• Remarks
• FIND always counts each character.
• The first character in within_text is character
  number 1.
• If you omit start_num, it is assumed to be 1.
• FIND is case sensitive.
• If find_text is "" (empty text), FIND matches the
  first character in the search string (that is,
  the character numbered start_num or 1).

36
The FIND function
Errors VALUE! If find_text does not appear in
within_text VALUE! If start_num is not
greater than zero VALUE! If start_num is
greater than the length of within_text
37
The FIND function
A
11 Miriam McGovern
FIND(M,A11) 1
38
The FIND function
A
11 Miriam McGovern
FIND(M,A11,3) 8
39
The FIND function
A
11 Miriam McGovern
FIND(m,A11) 6
40
The LEFT function

• Syntax
• LEFT(text,num_chars)
• Arguments
• text Required
• The text string that contains the characters you
  want to extract.
• num_chars Optional
• Specifies the number of characters you want LEFT
  to extract.

41
The LEFT function

• Description
• Returns the first character or characters in a
  text string, based on the number of characters
  you specify
• Remarks
• LEFT always counts each character.
• Num_chars must be gt 0.

42
The LEFT function
A
1 Data
2 Sale Price
LEFT(A2,4) Sale
43
The LEFT function
A
1 Data
2 Sale Price
3 Sweden
LEFT(A3) S
44
The LEN function

• Syntax
• LEN(text)
• Arguments
• text Required
• The text whose length you want to find.

45
The LEN function

• Description
• Returns the number of characters in a text
  string.
• Remarks
• Spaces count as characters.

46
The LEN function
A
1 Data
2 Phoenix, AZ
3
4
5
6 One
LEN(A2) 11
47
The LEN function
A
1 Data
2 Phoenix, AZ
3
4
5
6 One
LEN(A4) 0
48
The LEN function
A
1 Data
2 Phoenix, AZ
3
4
5
6 One
LEN(A6) 8
49
The RIGHT function

• Syntax
• RIGHT(text,num_chars)
• Arguments
• text Required
• The text string that contains the characters you
  want to extract.
• num_chars Optional
• Specifies the number of characters you want RIGHT
  to extract.

50
The RIGHT function

• Description
• Returns the last character or characters in a
  text string, based on the number of characters
  you specify.
• Remarks
• RIGHT always counts each character.
• Num_chars must be gt 0.

51
The RIGHT function
A
1 Data
2 Sale Price
3
4 Stock Number
RIGHT(A2,5) Price
52
The RIGHT function
A
1 Data
2 Sale Price
3
4 Stock Number
RIGHT(A2,LEN(A2)-FIND( ,A2)) RIGHT(A2,10-5) R
IGHT(A2,5) Price
53
The RIGHT function
A
1 Data
2 Sale Price
3
4 Stock Number
RIGHT(A4) r
54
The MID function

• Syntax
• MID(text,start_num,num_chars)
• Arguments
• text Required
• The text string that contains the characters you
  want to extract.
• start_num Required
• Specifies the number of character you want to
  start extracting from.
• text Required
• Specifies the number of characters you want to
  extract.

55
The MID function
MID(A1,11,6) string
MID(A3,4,2) is
56
The UPPER function

• Syntax
• UPPER(text)
• Arguments
• text Required
• The text you want converted to uppercase.

57
The UPPER function

• Description
• Converts text to uppercase.
• Remarks
• Text can be a reference or text string.

58
The UPPER function
UPPER(A2) TOTAL
59
The UPPER function
UPPER(A3) YIELD
60
The LOWER function

• Syntax
• LOWER(text)
• Arguments
• text Required
• The text you want converted to lowercase.

61
The LOWER function

• Description
• Converts all uppercase letters in a text string
  to lowercase.
• Remarks
• LOWER does not change characters in text that are
  not letters.

62
The LOWER function
LOWER(A2) e.e. cummings
63
The LOWER function
LOWER(A3) apt. 2b
64
The PROPER function

• Syntax
• PROPER(text)
• Arguments
• text Required
• Text enclosed in quotation marks, a formula that
  returns text, or a reference to a cell containing
  the text you want to partially capitalize.

65
The PROPER function

• Description
• Capitalizes the first letter in a text string and
  any other letters in text that follow any
  character other than a letter.
• Remarks
• PROPER converts all other letters to lowercase
  letters.

66
The PROPER function
PROPER(A2) This Is A Title
67
The PROPER function
PROPER(A3) 2-CentS Worth
68
The PROPER function
PROPER(A4) 76Budget
69
The CONCATENATE function

• Syntax
• CONCATENATE(text1, text2, ...)
• Arguments
• text1 Required
• The first text item to be concatenated.
• text2 Optional
• Additional text items, up to a maximum of 255
  items, which must be separated by commas.

70
The CONCATENATE function

• Description
• Joins up to 255 text strings into one text
  string.
• Remarks
• The joined items can be text, numbers, cell
  references, or a combination of those items.

71
The CONCATENATE function
A B C
1 Data Data Data
2 Brook trout Andreas Hauser
3 species Fourth Pine
4 32
CONCATENATE(Stream Population for ,A2, ,A3,
is ,A4,/mile) Stream Population for Brook
trout species is 32/mile
72
The CONCATENATE function
A B C
1 Data Data Data
2 Brook trout Andreas Hauser
3 species Fourth Pine
4 32
CONCATENATE(B2, , C2) Andreas Hauser
73
The CONCATENATE function
A B C
1 Data Data Data
2 Brook trout Andreas Hauser
3 species Fourth Pine
4 32
CONCATENATE(C2, , , B2) Hauser, Andreas
74
The CONCATENATE function
A B C
1 Data Data Data
2 Brook trout Andreas Hauser
3 species Fourth Pine
4 32
CONCATENATE(B3, , C3) Fourth Pine
75
The CONCATENATE function
A B C
1 Data Data Data
2 Brook trout Andreas Hauser
3 species Fourth Pine
4 32
B3 C3 Fourth Pine
76
The TRIM function

• Syntax
• TRIM(text)
• Arguments
• text Required
• text is the text value to remove the leading and
  trailing spaces from.

77
The TRIM function
TRIM(A1) Tech on the Net
TRIM(A2) 1234
78
The CLEAN function

• Syntax
• CLEAN(text)
• Arguments
• text Required
• is the value that has all nonprintable characters
  removed from.

79
The CLEAN function
CLEAN(A1) hi there
CLEAN(A2) this is a test
80
The REPLACE function

• Syntax
• REPLACE(old_text,start_num,num_chars,new_text)
• Arguments
• old_text Required
• Text for which some portion is to be replaced.
• start_num Required
• Starting position of the replacement in the old
  text.
• num_chars Required
• How many characters from the starting position to
  replace in the old text.
• new_text Required
• Text to replace the defined portion of the old
  text.

81
The REPLACE function
REPLACE(A1,10,7,B1) I made a passing grade in
the class!
REPLACE(A3,3,4,B3) I love learning about Excel!
82
The ISTEXT function

• Syntax
• ISTEXT(value)
• Arguments
• value Required
• the value that you want to test. If value is a
  text value, this function will return TRUE.
  Otherwise, it will return FALSE.

83
The ISTEXT function
ISTEXT(A1) FALSE
ISTEXT(A2) TRUE
ISTEXT(A3) FALSE
84
Microsoft? Excel? Lookup Functions
VLOOKUP VLOOKUP(lookup_value, table_array,
col_index_num, range_lookup) HLOOKUP HLOOKUP(
lookup_value,table_array,row_index_num,range_looku
p)
85
The VLOOKUP function

• Syntax
• VLOOKUP(lookup_value,table_array,col_index_num,r
  ange_lookup)
• Arguments
• lookup_value Required
• The value to search in the first column of the
  table or range.
• table_array Required
• The range of cells that contains the data.
• col_index_num Required
• The column number in the table_array argument
  from which the matching value must be returned.
• range_lookup Optional
• A logical value that specifies whether you want
  VLOOKUP to find an exact match or an approximate
  match.

86
The VLOOKUP function

• Description
• Searches the first column of a range of cells,
  and then returns a value from any cell on the
  same row of the range.
• Remarks
• The values in the first column of table_array can
  be text, numbers, or logical values.
• Uppercase and lowercase text are equivalent.
• If range_lookup is TRUE, the values in the first
  column of table_array must be placed in ascending
  order.
• If range_lookup is TRUE or omitted, an
  approximate match is returned.
• If range_lookup is FALSE, an exact match will be
  attempted.

87
The VLOOKUP function
Errors VALUE! If col_index_num is less than
1 REF! If col_index_num is greater than the
number of columns in the table_array N/A If
range_lookup is FALSE and an exact match cannot
be found N/A If lookup_value is less than the
smallest value in the first column of table_array
88

1. VLOOKUP(C112, B8G24, G18/E6, TRUE)
2. VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38, 83),
   E11G22,3)
3. VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
4. VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
   , 2, 4))
5. VLOOKUP(LARGE(D10G17,4), C8F20, 5, FALSE)

89

1. VLOOKUP(C112, B8G24, G18/E6, TRUE)

90

• VLOOKUP(C112, B8G24, G18/E6, TRUE)
• VLOOKUP(442, B8G24, G18/E6, TRUE)

91

• VLOOKUP(C112, B8G24, G18/E6, TRUE)
• VLOOKUP(442, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, G18/E6, TRUE)

92

• VLOOKUP(C112, B8G24, G18/E6, TRUE)
• VLOOKUP(442, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, G18/E6, TRUE)

93

• VLOOKUP(C112, B8G24, G18/E6, TRUE)
• VLOOKUP(442, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, 66/E6, TRUE)

94

• VLOOKUP(C112, B8G24, G18/E6, TRUE)
• VLOOKUP(442, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, 66/E6, TRUE)
• VLOOKUP(88, B8G24, 66/11, TRUE)

95

• VLOOKUP(C112, B8G24, G18/E6, TRUE)
• VLOOKUP(442, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, G18/E6, TRUE)
• VLOOKUP(88, B8G24, 66/E6, TRUE)
• VLOOKUP(88, B8G24, 66/11, TRUE)
• VLOOKUP(88, B8G24, 6, TRUE)

96
(2) VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38,
83), E11G22,3)
97

• VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgtMAX(D3G5), 38, 83),
  E11G22,3)

98

• VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgt11, 38, 83), E11G22,3)

99

• VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgt11), 38, 83), E11G22,3)
• VLOOKUP(IF(FALSE, 38, 83), E11G22,3)

100

• VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgt11), 38, 83), E11G22,3)
• VLOOKUP(IF(FALSE, 38, 83), E11G22,3)
• VLOOKUP(83, E11G22,3)

101

• VLOOKUP(IF(MIN(B6F18)ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgtMAX(D3G5), 38, 83),
  E11G22,3)
• VLOOKUP(IF(11ltgt11), 38, 83), E11G22,3)
• VLOOKUP(IF(FALSE, 38, 83), E11G22,3)
• VLOOKUP(83, E11G22,3)

102
(3) VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3,
FALSE)
103

• VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
• VLOOKUP(17B6/G3, C8F20, 3, FALSE)

104

• VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
• VLOOKUP(17B6/G3, C8F20, 3, FALSE)
• VLOOKUP(1718/G3, C8F20, 3, FALSE)

105

• VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
• VLOOKUP(17B6/G3, C8F20, 3, FALSE)
• VLOOKUP(1718/G3, C8F20, 3, FALSE)
• VLOOKUP(306/G3, C8F20, 3, FALSE)

106

• VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
• VLOOKUP(17B6/G3, C8F20, 3, FALSE)
• VLOOKUP(1718/G3, C8F20, 3, FALSE)
• VLOOKUP(306/G3, C8F20, 3, FALSE)
• VLOOKUP(306/6, C8F20, 3, FALSE)

107

• VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
• VLOOKUP(17B6/G3, C8F20, 3, FALSE)
• VLOOKUP(1718/G3, C8F20, 3, FALSE)
• VLOOKUP(306/6, C8F20, 3, FALSE)
• VLOOKUP(51, C8F20, 3, FALSE)

108
N/A

• VLOOKUP(MAX(B3G4)B6/G3, C8F20, 3, FALSE)
• VLOOKUP(17B6/G3, C8F20, 3, FALSE)
• VLOOKUP(1718/G3, C8F20, 3, FALSE)
• VLOOKUP(306/6, C8F20, 3, FALSE)
• VLOOKUP(51, C8F20, 3, FALSE)

109
(4) VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3
F24), 2, 4))
110

• VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
  , 2, 4))
• VLOOKUP(11E3, C8F20,
  IF(SUM(B3B24)gtSUM(F3F24), 2, 4))

111

• VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
  , 2, 4))
• VLOOKUP(11E3, C8F20,
  IF(SUM(B3B24)gtSUM(F3F24), 2, 4))
• VLOOKUP(115, C8F20, IF(SUM(B3B24)gtSUM(
  F3F24), 2, 4))

112

• VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
  , 2, 4))
• VLOOKUP(11E3, C8F20,
  IF(SUM(B3B24)gtSUM(F3F24), 2, 4))
• VLOOKUP(115, C8F20, IF(SUM(B3B24)gtSUM(
  F3F24), 2, 4))
• VLOOKUP(55, C8F20, IF(1000gtSUM(F3F24),
  2, 4))

113

• VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
  , 2, 4))
• VLOOKUP(11E3, C8F20,
  IF(SUM(B3B24)gtSUM(F3F24), 2, 4))
• VLOOKUP(115, C8F20, IF(SUM(B3B24)gtSUM(
  F3F24), 2, 4))
• VLOOKUP(55, C8F20, IF(1000gtSUM(F3F24),
  2, 4))
• VLOOKUP(55, C8F20, IF(1000gt924, 2, 4))

114

• VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
  , 2, 4))
• VLOOKUP(55, C8F20, IF(1000gt924, 2, 4))
• VLOOKUP(55, C8F20, IF(TRUE, 2, 4))
• VLOOKUP(55, C8F20, 2)

115

• VLOOKUP(C3E3, C8F20, IF(SUM(B3B24)gtSUM(F3F24)
  , 2, 4))
• VLOOKUP(55, C8F20, 2)

116

1. VLOOKUP(LARGE(D10G17,4), C8F20, 5, FALSE)

117

• VLOOKUP(LARGE(D10G17,4), C8F20, 5, FALSE)
• VLOOKUP(58, C8F20, 5, FALSE)

118
REF!

• VLOOKUP(LARGE(D10G17,4), C8F20, 5, FALSE)
• VLOOKUP(58, C8F20, 5, FALSE)

119
The VLOOKUP function
Shipping Cost Table at the top lists the shipping
charges for packages for weights by
Destination-Zone. For example, a package weighing
15 lbs to Destination-Zone 2 will cost 12.36 to
ship. The same package to Destination-Zone,
however, will cost 18.22 to ship.
Customers and Packages table below the "Shipping
Cost Table" lists Customer, Package Weight and
the Destination-Zone for each customer. What
formula using VLOOKUP for the first customer will
display the shipping cost? Note, this formula is
then copied (using auto-fill) for all customers.
Also provide the answer value.
120
The VLOOKUP function

• VLOOKUP(C18, B6F12, D181, TRUE)    
• 30.18

121
The HLOOKUP function

• Syntax
• HLOOKUP(lookup_value,table_array,row_index_num,r
  ange_lookup)
• Arguments
• lookup_value Required
• The value to search in the first row of the table
  or range.
• table_array Required
• The range of cells that contains the data.
• row_index_num Required
• The row number in table_array from which the
  matching value will be returned
• range_lookup Optional
• A logical value that specifies whether you want
  HLOOKUP to find an exact match or an approximate
  match.

122
The HLOOKUP function

• Description
• Searches for a value in the top row of a table
  or an array of values, and then returns a value
  in the same column from a row you specify in the
  table or array.
• Remarks
• If HLOOKUP can't find lookup_value, and
  range_lookup is TRUE, it uses the largest value
  that is less than lookup_value.
• If lookup_value is smaller than the smallest
  value in the first row of table_array, HLOOKUP
  returns the N/A error value.

123
The HLOOKUP function
Errors VALUE! If row_index_num is less than
1 REF! If row_index_num is greater than the
number of rows in the table_array N/A If
range_lookup is FALSE and an exact match cannot
be found N/A If lookup_value is less than the
smallest value in the first row of table_array
124
The HLOOKUP function
  A B C
1 Axles Bearing Bolts
2 4 4 9
3 5 7 10
4 6 8 11
Formula Description (Result) Looks up Axles in row 1, and returns the value from row 2 that's in the same column. (4)
HLOOKUP("Axles",A1C4,2,TRUE) Description (Result) Looks up Axles in row 1, and returns the value from row 2 that's in the same column. (4)
HLOOKUP("Bearings",A1C4,3,FALSE) Looks up Bearings in row 1, and returns the value from row 3 that's in the same column. (7)
HLOOKUP("B",A1C4,3,TRUE) Looks up B in row 1, and returns the value from row 3 that's in the same column. Because B is not an exact match, the next largest value that is less than B is used Axles. (5)
HLOOKUP("Bolts",A1C4,4) Looks up Bolts in row 1, and returns the value from row 4 that's in the same column. (11)
125
Microsoft? Excel?Information Functions/
Exception Handling
ISERROR ISERROR(value) ISNA ISNA(value)
126
The ISERROR function

• Syntax
• ISERROR(value)
• Arguments
• value Required
• The value that you want tested

127
The ISERROR function

• Description
• Returns TRUE if value refers to any error value
• N/A VALUE! REF! DIV/0!
• NUM! NAME? NULL!
• Remarks
• The value arguments of the IS functions are not
  converted
• Any numeric values that are enclosed in double
  quotation marks are treated as text.
• The IS functions are useful in formulas for
  testing the outcome of a calculation
• Errors
• None

128
The ISNA function

• Syntax
• ISNA(value)
• Arguments
• value Required
• The value that you want tested

129
The ISNA function

• Description
• Returns TRUE if value refers to the N/A (value
  not available) error value.
• Remarks
• The value arguments of the IS functions are not
  converted
• Any numeric values that are enclosed in double
  quotation marks are treated as text.
• The IS functions are useful in formulas for
  testing the outcome of a calculation
• Errors
• None

130
The ISNA function
131
The ISNA function