Transcript CIS300 Final Exam Review - Resources for Academic

Fall 2011
© 2011 REACH All Rights Reserved.
•Mathematical Functions
•ROUND
•SUMIF
• Statistical Functions
•AVERAGEIF
•COUNTIF
•LARGE
•SMALL
•Lookup Functions
•HLOOKUP
•LOOKUP
•VLOOKUP
•Information Functions
•ISERROR
•ISNA
•ISREF
ROUND
=ROUND(number,num_digits)
SUMIF
=SUMIF(range,criteria,[sum_range])
Syntax:
=ROUND(number, num_digits)
Arguments:
•number Required
The number that you want to round.
•num_digits Required
The number of digits to which you want to round the number argument.
Microsoft® Excel® Mathematical Functions
Description:
•Rounds a number to a specified number of digits.
Remarks:
•If num_digits is greater than 0 (zero), then number is rounded to the specified
number of decimal places.
•If num_digits is 0, the number is rounded to the nearest integer.
•If num_digits is less than 0, the number is rounded to the left of the decimal
point.
Errors:
None
Microsoft® Excel® Mathematical Functions
=ROUND(-1.475,2) Rounds -1.475 to two decimal places
Microsoft® Excel® Mathematical Functions
To a great extent, the operating system
determines which applications a computer can
run.
A) TRUE
B) FALSE
To a great extent, the operating system
determines which applications a computer can
run.
A) TRUE 
B) FALSE
Ref: p.61
Syntax:
=SUMIF(range, criteria, [sum_range])
Arguments:
•range Required
The range of cells that you want evaluated by criteria.
oCells in each range must be numbers or names, arrays, or references that contain numbers.
oBlank and text values are ignored.
criteria Required
The criteria in the form of a number, expression, a cell reference, text, or a function that defines
which cells will be added.
oCriteria can be expressed as 32, ">32", B5, "32", "apples", or TODAY().
•sum_range Optional
The actual cells to add, if you want to add cells other than those specified in the range argument.
oExcel adds the cells that are specified in the range argument (the same cells to which the
criteria is applied).
Microsoft® Excel® Mathematical Functions
Description:
•Sums the values in a range that meet criteria that you specify.
Remarks:
•See the Microsoft® Excel® help for additional remarks.
Errors:
None
Microsoft® Excel® Mathematical Functions
Microsoft® Excel® Mathematical Functions
Adding to a product or service to increase its
value to the consumer is called ____.
A)
B)
C)
D)
adding value
enhancing existing products or services
increasing value
incrementing service
Adding to a product or service to increase its
value to the consumer is called ____.
A) adding value
B) enhancing existing products or services

C) increasing value
D) incrementing service
Ref. pp. 49-50
AVERAGEIF
=AVERAGEIF(range,criteria,[average_range])
COUNTIF
=COUNTIF(range, criteria)
LARGE
=LARGE(array,k)
SMALL
=SMALL(array,k)
Syntax:
=AVERAGEIF(range, criteria, [average_range])
Arguments:
•range Required
One or more cells to average, including numbers or names, arrays, or
references that contain numbers.
•criteria Required
The criteria in the form of a number, expression, cell reference, or text that
defines which cells are averaged.
•average_range Optional
The actual set of cells to average.
Microsoft® Excel® Statistical Functions
Description:
•Returns the average (arithmetic mean) of all the cells in a range that meet a
given criteria.
Remarks:
•If average_range is omitted, range is used.
•Cells in range that contain TRUE or FALSE are ignored.
•If a cell in average_range is an empty cell, AVERAGEIF ignores it.
•If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.
Errors:
#DIV/0 – If range is a blank or text value.
#DIV/0 – If no cells in the range meet the criteria.
Microsoft® Excel® Statistical Functions
=AVERAGEIF(B2:B5,"<23000")
Microsoft® Excel® Statistical Functions
=AVERAGEIF(B2:B5,"<23000")
=14000
Microsoft® Excel® Statistical Functions
=AVERAGEIF(A2:A5,"<95000")
Microsoft® Excel® Statistical Functions
=AVERAGEIF(A2:A5,"<95000")
=#DIV/0
Microsoft® Excel® Statistical Functions
=AVERAGEIF(A2:A5,">250000",B2:B5)
Microsoft® Excel® Statistical Functions
=AVERAGEIF(A2:A5,">250000",B2:B5)
=24500
Microsoft® Excel® Statistical Functions
Syntax:
=COUNTIF(range, criteria)
Arguments:
•range Required
One or more cells to count, including numbers or names, arrays, or references
that contain numbers.
oBlank and text values are ignored.
criteria Required
A number, expression, cell reference, or text string that defines which cells will
be counted.
oCriteria can be expressed as 32, ">32", B4, "apples", or "32".
Microsoft® Excel® Statistical Functions
Description:
•Counts the number of cells within a range that meet a single criterion that you
specify.
Remarks:
•See the Microsoft® Excel® help for additional remarks.
•Criteria are case insensitive
Errors:
None
Microsoft® Excel® Statistical Functions
Microsoft® Excel® Statistical Functions
Implementation of a(n) ____ requires a business
to revamp processes—to undergo organizational
change—to gain an advantage.
A)
B)
C)
D)
BI
CRM
GIS
SIS
Implementation of a(n) ____ requires a business
to revamp processes—to undergo organizational
change—to gain an advantage.
A) BI
B) CRM
C) GIS
D) SIS

Ref: p. 56




Many opportunities to accomplish competitive
edge with information technology
Innovative software can establish a competitive
advantage
Strategic information systems can be created
from scratch or by modifying a previous system
To be an SIS, an information system must:
 Serve an organization goal
 Collaborate with other functional units of company
Management Information Systems, Sixth Edition
28
Syntax:
=LARGE(array,k)
Arguments:
•array Required
The array or range of data for which you want to determine the k-th largest
value.
k Required
The position (from the largest) in the array or cell range of data to return.
Microsoft® Excel® Statistical Functions
Description:
•Returns the k-th largest value in a data set.
Remarks:
•If n is the number of data points in a range, then LARGE(array,1) returns the largest value.
•If n is the number of data points in a range, then LARGE(array,n) returns the smallest
value.
Errors:
#NUM! – If array is empty
#NUM! – If k ≤ 0
#NUM! – If k is greater than the number of data points
Microsoft® Excel® Statistical Functions
3rd largest number in the numbers in columns A and B
=LARGE(array,k)
3rd largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6
3rd largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6,3)
3rd largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6,3)
=5
List the numbers in
descending order:
7
6
5
5
4
4
4
3
3
2
7th largest number in the numbers in columns A and B
=LARGE(array,k)
7th largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6
7th largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6,7)
7th largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6,7)
List the numbers in
descending order:
7
6
5
5
4
4
4
3
3
2
7th largest number in the numbers in columns A and B
=LARGE(array,k)
=LARGE(A2:B6,7)
=4
List the numbers in
descending order:
7
6
5
5
4
4
4
3
3
2
A company achieves ____________________ by
using strategy to maximize its strengths,
resulting in a competitive advantage.
A company achieves strategic advantage by
using strategy to maximize its strengths,
resulting in a competitive advantage.
Ref. p. 42
Syntax:
=SMALL(array,k)
Arguments:
•array Required
The array or range of data for which you want to determine the k-th smallest
value.
k Required
The position (from the smallest) in the array or cell range of data to return.
Microsoft® Excel® Statistical Functions
Description:
•Returns the k-th smallest value in a data set.
Remarks:
•If n is the number of data points in a range, then SMALL(array,1) returns the smallest
value.
•If n is the number of data points in a range, then SMALL(array,n) returns the largest value.
Errors:
#NUM! – If array is empty
#NUM! – If k ≤ 0
#NUM! – If k is greater than the number of data points
Microsoft® Excel® Statistical Functions
4th smallest number in first column
=SMALL(array,k)
4th smallest number in first column
=SMALL(array,k)
=SMALL(A2:A10
4th smallest number in first column
=SMALL(array,k)
=SMALL(A2:A10,4)
List the numbers in
ascending order:
2
3
3
4
4
4
5
6
7
4th smallest number in first column
=SMALL(array,k)
=SMALL(A2:A10,4)
=4
List the numbers in
ascending order:
2
3
3
4
4
4
5
6
7
2nd smallest number in second column
=SMALL(array,k)
2nd smallest number in second column
=SMALL(array,k)
=SMALL(B2:B10
2nd smallest number in second column
=SMALL(array,k)
=SMALL(B2:B10,2)
List the numbers in
ascending order:
1
3
4
7
8
8
12
23
54
2nd smallest number in second column
=SMALL(array,k)
=SMALL(B2:B10,2)
=3
List the numbers in
ascending order:
1
3
4
7
8
8
12
23
54
When the process of marketing products is
complete, the next link in the supply chain is
shipping.
A) TRUE
B) FALSE
When the process of marketing products is
complete, the next link in the supply chain is
shipping.
A) TRUE
B) FALSE





Supply chain: consists of procurement of raw
materials, processing materials into goods,
and delivering goods
Processing raw materials into goods is also
known as manufacturing
Supply chain management: monitoring,
controlling, and facilitating supply chains
CAD systems often transfer data
automatically to CAM systems
Management Information Systems, Sixth Edition
54
Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_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.
Microsoft® Excel® Lookup Functions
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.
Microsoft® Excel® Lookup Functions
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
Microsoft® Excel® Lookup Functions
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
(2)
=VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
(5)
=VLOOKUP(LARGE(D10:G17,4), $C$8:$F$20, 5, FALSE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(44*2, $B$8:$G$24, G18/E6, TRUE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(44*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, G18/E6, TRUE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(44*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, G18/E6, TRUE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(44*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, 66/E6, TRUE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(44*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, 66/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, 66/11, TRUE)
(1)
=VLOOKUP(C11*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(44*2, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, G18/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, 66/E6, TRUE)
=VLOOKUP(88, $B$8:$G$24, 66/11, TRUE)
=VLOOKUP(88, $B$8:$G$24, 6, TRUE)
(2) =VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
(2)
=VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>MAX(D3:G5), 38, 83), E11:G22,3)
(2)
=VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>11, 38, 83), E11:G22,3)
(2)
=VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>11), 38, 83), E11:G22,3)
=VLOOKUP(IF(FALSE, 38, 83), E11:G22,3)
(2)
=VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>11), 38, 83), E11:G22,3)
=VLOOKUP(IF(FALSE, 38, 83), E11:G22,3)
=VLOOKUP(83, E11:G22,3)
(2)
=VLOOKUP(IF(MIN(B6:F18)<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>MAX(D3:G5), 38, 83), E11:G22,3)
=VLOOKUP(IF(11<>11), 38, 83), E11:G22,3)
=VLOOKUP(IF(FALSE, 38, 83), E11:G22,3)
=VLOOKUP(83, E11:G22,3)
(3) =VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*B6/G3, $C$8:$F$20, 3, FALSE)
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*18/G3, $C$8:$F$20, 3, FALSE)
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*18/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(306/G3, $C$8:$F$20, 3, FALSE)
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*18/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(306/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(306/6, $C$8:$F$20, 3, FALSE)
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*18/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(306/6, $C$8:$F$20, 3, FALSE)
=VLOOKUP(51, $C$8:$F$20, 3, FALSE)
#N/A
(3)
=VLOOKUP(MAX(B3:G4)*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*B6/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(17*18/G3, $C$8:$F$20, 3, FALSE)
=VLOOKUP(306/6, $C$8:$F$20, 3, FALSE)
=VLOOKUP(51, $C$8:$F$20, 3, FALSE)
(4) =VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*5, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*5, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(55, C8:F20, IF(1000>SUM(F3:F24), 2, 4))
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(11*5, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
=VLOOKUP(55, C8:F20, IF(1000>SUM(F3:F24), 2, 4))
=VLOOKUP(55, C8:F20, IF(1000>924, 2, 4))
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
…
=VLOOKUP(55, C8:F20, IF(1000>924, 2, 4))
=VLOOKUP(55, C8:F20, IF(TRUE, 2, 4))
=VLOOKUP(55, C8:F20, 2)
(4)
=VLOOKUP(C3*E3, C8:F20, IF(SUM(B3:B24)>SUM(F3:F24), 2, 4))
…
=VLOOKUP(55, C8:F20, 2)
(5)
=VLOOKUP(LARGE(D10:G17,4), $C$8:$F$20, 5, FALSE)
(5)
=VLOOKUP(LARGE(D10:G17,4), $C$8:$F$20, 5, FALSE)
=VLOOKUP(58, $C$8:$F$20, 5, FALSE)
#REF!
(5)
=VLOOKUP(LARGE(D10:G17,4), $C$8:$F$20, 5, FALSE)
=VLOOKUP(58, $C$8:$F$20, 5, FALSE)
Syntax:
= HLOOKUP(lookup_value,table_array,row_index_num,range_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.
Microsoft® Excel® Lookup Functions
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.
Microsoft® Excel® Lookup Functions
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
Microsoft® Excel® Lookup Functions
1
2 4
3 5
4 6
A
Axles
B
C
Bearings Bolts
4
9
7
10
8
11
Formula
=HLOOKUP("Axles",A1:C4,2,TRUE)
=HLOOKUP("Bearings",A1:C4,3,FALSE)
=HLOOKUP("B",A1:C4,3,TRUE)
=HLOOKUP("Bolts",A1:C4,4)
Description (Result)
Looks up Axles in row 1, and returns the value from row
2 that's in the same column. (4)
Looks up Bearings in row 1, and returns the value from
row 3 that's in the same column. (7)
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)
Looks up Bolts in row 1, and returns the value from row
4 that's in the same column. (11)
Notes
The Lookup function has two forms:
If you want to
Look in a one-row or one-column range (known
as a vector) for a value and return a value from
the same position in a second one-row or onecolumn range
Then see
Vector form
Look in the first row or column of an array for the
specified value and return a value from the same Array form
position in the last row or column of the array
Microsoft® Excel® Lookup Functions
Usage
Use the vector form when you
have a large list of values to look
up or when the values may
change over time.
Use the array form when you
have a small list of values and the
values remain constant over
time.
Syntax:
=LOOKUP(lookup_value, lookup_vector, result_vector)
Arguments:
•lookup_value Required
 Value that LOOKUP searches for in the first vector.
 Can be a number, text, a logical value, or a name or reference that refers to a
value.
•lookup_vector Required
A range that contains only one row or one column.
The values can be text, numbers, or logical values.
result_vector Required
A range that contains only one row or column.
Must be the same size as lookup_vector.
Microsoft® Excel® Lookup Functions
Remarks:
•If the LOOKUP function can't find the lookup_value, the function matches the
largest value in lookup_vector that is less than or equal to lookup_value.
•If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns
the #N/A error value.
Microsoft® Excel® Lookup Functions
Syntax:
= LOOKUP(lookup_value, array)
Arguments:
• lookup_value Required
 Value that LOOKUP searches for in an array.
 Can be a number, text, a logical value, or a name or reference that refers to a
value.
• array Required
 A range of cells that contains text, numbers, or logical values that you want to
compare with lookup_value.
Microsoft® Excel® Lookup Functions
Remarks:
• If LOOKUP can't find the value of lookup_value, it uses the largest value in the
array that is less than or equal to lookup_value.
• If the value of lookup_value is smaller than the smallest value in the first row or
column (depending on the array dimensions), LOOKUP returns the #N/A error
value.
• If array covers an area that is wider than it is tall (more columns than rows),
LOOKUP searches for the value of lookup_value in the first row.
• If an array is square or is taller than it is wide (more rows than columns), LOOKUP
searches in the first column.
• With the HLOOKUP and VLOOKUP functions, you can index down or across, but
LOOKUP always selects the last value in the row or column.
Microsoft® Excel® Lookup Functions
A
B
1
Frequency
Color
2
4.14
red
3
4.19
orange
4
5.17
yellow
5
5.77
green
6
6.39
blue
Formula
=LOOKUP(4.19,A2:A6,B2:B6)
=LOOKUP(5.00,A2:A6,B2:B6)
=LOOKUP(7.66,A2:A6,B2:B6)
=LOOKUP(0,A2:A6,B2:B6)
Description
Looks up 4.19 in column A, and returns the value from column B that
is in the same row.
Looks up 5.00 in column A, matches the next smallest value (4.19),
and returns the value from column B that is in the same row.
Looks up 7.66 in column A, matches the next smallest value (6.39),
and returns the value from column B that is in the same row.
Looks up 0 in column A, and returns an error because 0 is less than the
smallest value in the lookup_vector A2:A7.
Result
orange
orange
blue
#N/A
LOOKUP is very similar to VLOOKUP and
HLOOKUP. The difference is that HLOOKUP
searches for the lookup_value in the first row,
VLOOKUP searches in the first column, and
LOOKUP searches according to the
dimensions of an array.
A.
B.
TRUE
FALSE
LOOKUP is very similar to VLOOKUP and
HLOOKUP. The difference is that HLOOKUP
searches for the lookup_value in the first row,
VLOOKUP searches in the first column, and
LOOKUP searches according to the
dimensions of an array.
A.
B.
TRUE
FALSE
____, used to accumulate data about costs
involved in producing specific products, make
excellent use of IT to compile pricing data.
A)
B)
C)
D)
ERP Systems
Cost-accounting Systems
Human Resources Systems
SCM Systems
____, used to accumulate data about costs
involved in producing specific products, make
excellent use of IT to compile pricing data.
A) ERP Systems
B) Cost-accounting Systems
C) Human Resources Systems
D) SCM Systems

Ref: p.81
When a pattern of defects is discovered in a
product, ____________________ helps pinpoint
the plant at which it was produced and the
particular lot from which it came.
When a pattern of defects is discovered in a
product, ______R F I D_______ helps pinpoint
the plant at which it was produced and the
particular lot from which it came.
Ref: p.91
ISNA
=ISNA(value)
ISERROR
=ISERROR(value)
ISREF
=ISREF(value)
Syntax:
=ISNA(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
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
Microsoft® Excel® Information Functions
Microsoft® Excel® Information Functions
Microsoft® Excel® Information Functions
Syntax:
=ISERROR(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
Description:
•Returns TRUE if value refers to any error value:
•#N/A
#VALUE! #REF!
•#NUM!
#NAME? #NULL!
#DIV/0!
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
Microsoft® Excel® Information Functions
Syntax:
=ISREF(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
Description:
•Returns TRUE if the value is a reference
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
Microsoft® Excel® Information Functions
FORMULA
DESCRIPTION
RESULT
=ISREF(XYZ1)
Checks if XYZ1 is a valid
reference
FALSE
ISREF(C1)
Checks if C1 is a valid
reference
TRUE
Extra Material
Entity – any object about which an organization
chooses to collect data. Ex. Types of people:
employees, students, contractors, etc.
Character – the smallest piece of data
Field – one piece of information about an entity Ex. First
Name or Last Name. Multiple characters make up a
field
Record – the fields related to the same entity make up a
record
File – A collection of related records Ex. All the records
of a colleges’ students
Database – a collection of one or more files
Ref. p 235
There are two overall approaches to maintaining
data: the traditional file approach—which has no
mechanism for tagging, retrieving, and
manipulating data—and the ____, which does
have this mechanism.
A)
B)
C)
D)
Database Approach
Data Approach
Datafile Approach
Indexed file approach
There are two overall approaches to maintaining
data: the traditional file approach—which has no
mechanism for tagging, retrieving, and
manipulating data—and the ____, which does
have this mechanism.
A)
B)
C)
D)
Database Approach
Data Approach
Datafile Approach
Indexed file approach
Ref. p. 234
A collection of related records, such as all the
records of a college’s students, is called a(n)
A)
B)
C)
D)
Field
Character
Item
File
A collection of related records, such as all the
records of a college’s students, is called a(n)
A)
B)
C)
D)
Field
Character
Item
File
Ref p. 235
A ____ is the general logical structure in which
records are stored within a database and the
method used to establish relationships among
the records.
A)
B)
C)
D)
database relationship
database model
database list
database catalog
A ____ is the general logical structure in which
records are stored within a database and the
method used to establish relationships among
the records.
A)
B)
C)
D)
database relationship
database model
database list
database catalog
Ref p. 238
While a database itself is a collection of several
related files, the program used to build
databases, populate them with data, and
manipulate the data is called a(n)
____________________.
While a database itself is a collection of several
related files, the program used to build
databases, populate them with data, and
manipulate the data is called a(n) DBMS –
Database Management System.
Ref. p 236
Because of capacity needs, organizations often
choose ____ to store and manage data
warehouses.
A)
B)
C)
D)
midrange servers
high speed networks
mainframe computers with multiple CPUs
workstations
Because of capacity needs, organizations often
choose ____ to store and manage data
warehouses.
A)
B)
C)
D)
midrange servers
high speed networks
mainframe computers with multiple CPUs
Workstations
Ref. p 250
Multiple characters make up a record.
A. TRUE
B. FALSE
Multiple characters make up a record.
A. TRUE
B. FALSE
Ref. p 235
(Excel) You are creating a new forecast for your
company's Marketing group and have entered the
sales for 2005 into cell B4 of a spreadsheet. The
expected rate of increase is in cell C2. What formula
would you enter in cell C4 to compute the sales for
2006, given that you will copy that formula to cells
D4:E4 to calculate the forecast for 2007 and 2008?
A. =B$4+B$4*C$2
B. =$B$4+$B$4*C2
C. =$B4+$B4*$C2
D. =B4*(1+$C$2)
E. =B4+B4*C2
