#### Transcript CIS300 Final Exam Review - Resources for Academic

```Cell Referencing
Reference (Description)
Changes to
\$A\$1 (absolute column and absolute row)
\$A\$1
A\$1 (relative column and absolute row)
C\$1
\$A1 (absolute column and relative row)
\$A3
A1 (relative column and relative row)
C3
Microsoft Excel 
• Logical Functions
• Mathematical Functions
• Information Function
Microsoft Excel  Logical Functions
AND
=AND(logical1, [logical2], ...)
OR
=OR(logical1, [logical2], ...)
NOT
=NOT(logical)
IF
=IF(logical_test, [value_if_true], [value_if_false])
The
function
Syntax:
=AND(logical1, [logical2], ...)
Arguments:
•logical1 Required
The first condition that you want to test that can evaluate to either TRUE
or FALSE.
•logical2, ... Optional
Additional conditions that you want to test that can evaluate to either
TRUE or FALSE, up to a maximum of 255 conditions.
Microsoft® Excel® Logical Functions
The
function
Description:
•Returns FALSE if one or more arguments is FALSE
•Otherwise, all arguments must evaluate TRUE
Remarks:
•Arguments must evaluate to logical values
•Arguments must be arrays or references that contain logical values
•Text and empty cells are ignored in arrays or references
Errors:
#VALUE – If no logical values exist in a specified range
Microsoft® Excel® Logical Functions
The
function – EXAMPLE 1

 
 
Microsoft® Excel® Logical Functions
The
function – EXAMPLE 2
Q. Find which conferences had at least a 10% increase in attendance from
2010 to 2011. Use the AND function.
A. =AND(C2>B2, ((C2-B2)/B2)>=0.1), etc.
Microsoft® Excel® Logical Functions
The
function
Syntax:
=OR(logical1, [logical2], ...)
Arguments:
•logical1 Required
The first condition that you want to test that can evaluate to either TRUE
or FALSE.
•logical2, ... Optional
Additional conditions that you want to test that can evaluate to either
TRUE or FALSE, up to a maximum of 255 conditions.
Microsoft® Excel® Logical Functions
The
function
Description:
•Returns TRUE if one or more arguments is TRUE
•Otherwise, all arguments must evaluate FALSE
Remarks:
•Arguments must evaluate to logical values
•Arguments must be arrays or references that contain logical values
•Text and empty cells are ignored in arrays or references
Errors:
#VALUE – If no logical values exist in a specified range
Microsoft® Excel® Logical Functions
The
function – EXAMPLE 1

 
  
Microsoft® Excel® Logical Functions
The
function – EXAMPLE 2
Q. Use the OR function to decide whether the employee gets overtime pay.
The employee will receive overtime if they worked over 40 hours or worked
on a holiday.
A. =OR(B2>40, C2=“Yes”), etc.
Microsoft® Excel® Logical Functions
The
function
Syntax:
=NOT(logical)
Arguments:
•logical Required
A value or expression that can be evaluated to TRUE or FALSE.
Microsoft® Excel® Logical Functions
The
function
Description:
•Reverses the value of its argument.
Remarks:
•If logical is FALSE, NOT returns TRUE
•if logical is TRUE, NOT returns FALSE
Errors:
None
Microsoft® Excel® Logical Functions
The
function
Microsoft® Excel® Logical Functions
The
function
Syntax:
=IF(logical_test, [value_if_true], [value_if_false])
Arguments:
•logical_test Required
Any value or expression that can be evaluated to TRUE or FALSE.
•value_if_true Optional
•The value that you want to be returned if the logical_test argument
evaluates to TRUE.
•If logical_test evaluates to TRUE and the value_if_true argument is
omitted (that is, there is only a comma following the logical_test argument),
the IF function returns 0 (zero).
•To display the word TRUE, use the logical value TRUE for the
value_if_true argument.
Microsoft® Excel® Logical Functions
The
function
Syntax:
=IF(logical_test, [value_if_true], [value_if_false])
Arguments:
•value_if_false Optional
The value that you want to be returned if the logical_test argument
evaluates to FALSE.
If logical_test evaluates to FALSE and the value_if_false argument is
omitted, (that is, there is no comma following the value_if_true argument),
the IF function returns the logical value FALSE.
If logical_test evaluates to FALSE and the value of the value_if_false
argument is omitted (that is, in the IF function, there is a comma following
the value_if_true argument), the IF function returns the value 0 (zero).
Microsoft® Excel® Logical Functions
The
function
Description:
•The IF function returns one value if a condition you specify evaluates to
TRUE, and another value if that condition evaluates to FALSE.
Remarks:
•Up to 7 IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. (2003)
•Up to 64 IF functions can be nested as value_if_true and value_if_false
arguments to construct more elaborate tests. (2007)
•If any of the arguments to IF are arrays, every element of the array is
evaluated when the IF statement is carried out.
Errors:
None
Microsoft® Excel® Logical Functions
The
function – EXAMPLE 1


value_if_true
[value_if_false]
Microsoft® Excel® Logical Functions
The
function – EXAMPLE 2
Q. Which students will receive the Dean’s List Award for the semester?
The award is given to students with a 3.5 or better GPA.
A. =IF(B2>=3.5, “Dean’s List”, “No Award”), etc.
Microsoft® Excel® Logical Functions
Microsoft Excel
Information Functions
ISBLANK
=ISBLANK(value)
ISNUMBER
=ISNUMBER(value)
The
function
Syntax:
=ISBLANK(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
The
function
Description:
•Returns TRUE if value refers to a blank cell.
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
The
1
2
3
4
5
6
7
8
function-Example
A
Data
B
C
Formula
Description
Result
=ISBLANK(A2)
Checks whether cell A2 is blank. FALSE
Gold
Region1
#REF!
330.92
#N/A
The
function
Syntax:
=ISNUMBER(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
The
function
Description:
•Returns TRUE if value refers to a number.
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
The
function-Example
A
B
1 Data
2 Gold
3 Region1
4 #REF!
5 330.92
6 #N/A
7 Formula
Description
13=ISNUMBER(A5) Checks whether the value in cell A5, 330.92, is a number.
C
Result
TRUE
The
function
Syntax:
=ISERR(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
The
function
Description:
•Returns TRUE if value refers to any error value except #N/A.
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
The
function
Syntax:
=ISERROR(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
The
function
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
The
function
Syntax:
=ISNA(value)
Arguments:
•value Required
The value that you want tested
Microsoft® Excel® Information Functions
The
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
Microsoft® Excel® Information Functions
The
function
Microsoft® Excel® Information Functions
The
function
Microsoft® Excel® Information Functions
The
function
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
The
function
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
The
function – EXAMPLE 1
Microsoft® Excel® Mathematical Functions
The
function – EXAMPLE 2
Q. What is the total amount spent over budget for all the groups? What is the
total amount spent under budget for all the groups?
A. =SUMIF(D2:D8, “>0”, D2:D8) (for over budget)
=-SUMIF(D2:D8, “<0”, D2:D8) (for under budget)
Microsoft® Excel® Mathematical Functions
Microsoft Excel
Statistical Functions
AVERAGEIF
=AVERAGEIF(range, criteria, [average_range])
COUNTIF
=AVERAGEIF(range, criteria, [average_range])
COUNTA
=COUNTA(value1, [value2],...)
MAX
=MAX(number1,[number2],...)
MIN
=MIN(number1,[number2],...)
LARGE
=LARGE(array,k)
SMALL
=LARGE(array,k)
The
function
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
The
function
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
The
function
=AVERAGEIF(B2:B5,"<23000")
Microsoft® Excel® Statistical Functions
The
function
=AVERAGEIF(B2:B5,"<23000")
=14000
Microsoft® Excel® Statistical Functions
The
function
=AVERAGEIF(A2:A5,"<95000")
Microsoft® Excel® Statistical Functions
The
function
=AVERAGEIF(A2:A5,"<95000")
=#DIV/0
Microsoft® Excel® Statistical Functions
The
function
=AVERAGEIF(A2:A5,">250000",B2:B5)
Microsoft® Excel® Statistical Functions
The
function
=AVERAGEIF(A2:A5,">250000",B2:B5)
=24500
Microsoft® Excel® Statistical Functions
The
function – EXAMPLE
Q. What is the average earnings of employees making more than 50,000?
A. =AVERAGEIF(B2:B5, “>50000”)
Microsoft® Excel® Statistical Functions
The
function
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
The
function
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
The
function – EXAMPLE 1
Microsoft® Excel® Statistical Functions
The
function – EXAMPLE 2
Q. How many C’s were earned in the class?
A. =COUNTIF(A2:A21, “C”)
=4
Microsoft® Excel® Statistical Functions
The
function
Syntax:
=COUNTA(value1, [value2],...)
Arguments:
•value1 Required
The first argument representing the values that you want to count.
•value2, ... Optional
Additional arguments representing the values that you want to count, up
to a maximum of 255 arguments.
Microsoft® Excel® Statistical Functions
The
function
Description:
•Counts the number of cells that are not empty in a range.
Remarks:
•Counts cells containing any type of information, including error
values and empty text ("“).
•The COUNTA function does not count empty cells.
Errors:
None
Microsoft® Excel® Statistical Functions
The
function
=COUNTA(A1:A8)
=7
Microsoft® Excel® Statistical Functions
The
function
Syntax:
=MAX(number1,[number2],...)
Arguments:
•number1, number2, ... Required
1 to 255 numbers for which you want to find the maximum value.
Microsoft® Excel® Statistical Functions
The
function
Description:
•Returns the largest value in a set of values.
Remarks:
•Arguments can either be numbers or names, arrays, or references that contain
numbers.
•Logical values and text representations of numbers that you type directly into the
list of arguments are counted.
•If an argument is an array or reference, only numbers in that array or reference are
used. Empty cells, logical values, or text in the array or reference are ignored.
•If the arguments contain no numbers, MAX returns 0 (zero).
Errors:
Arguments that are error values or text that cannot be translated into numbers
cause errors.
Microsoft® Excel® Statistical Functions
The
function
Microsoft® Excel® Statistical Functions
The
function
Syntax:
=MIN(number1,[number2],...)
Arguments:
•number1, number2, ... Required
1 to 255 numbers for which you want to find the minimum value.
Microsoft® Excel® Statistical Functions
The
function
Description:
•Returns the smallest value in a set of values.
Remarks:
•Arguments can either be numbers or names, arrays, or references that contain
numbers.
•Logical values and text representations of numbers that you type directly into the
list of arguments are counted.
•If an argument is an array or reference, only numbers in that array or reference are
used. Empty cells, logical values, or text in the array or reference are ignored.
•If the arguments contain no numbers, MIN returns 0 (zero).
Errors:
Arguments that are error values or text that cannot be translated into numbers
cause errors.
Microsoft® Excel® Statistical Functions
The
function
Microsoft® Excel® Statistical Functions
The
function
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
The
function
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
The
function
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
The
function
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
Microsoft Excel Lookup Functions
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The
function
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
The
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.
Microsoft® Excel® Lookup Functions
The
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
Microsoft® Excel® Lookup Functions
function – Example 1
The
1
2
3
4
5
=VLOOKUP(“CPU”,A1:F9,5,FALSE)
=VLOOKUP(“CPU”,A1:F9,5,FALSE)
=VLOOKUP(“CPU”,A1:F9,5,FALSE)
=VLOOKUP(“CPU”,A1:F9,5,FALSE)
The
function – Example 2
Q. How much total profit was made on the item that costs \$40.00?
A. =VLOOKUP(40,C2:F7,4,FALSE)
The
function
Syntax:
=HLOOKUP(lookup_value,table_array,row_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 row 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
The
function
Description:
•Searches the first row of a range of cells, and then returns a value from
any cell on the same column 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
The
function – Example 1
=VLOOKUP(“Retail Value”,A1:F9,7,FALSE)
=VLOOKUP(“Retail Value”,A1:F9,7,FALSE)
=VLOOKUP(“Retail Value”,A1:F9,7,FALSE)
=VLOOKUP(“Retail Value”,A1:F9,7,FALSE)
The
function – Example 2
Q. How many motherboards did they sell?
A. =VLOOKUP(“Quantity”,A1:F9,6,FALSE)
Some More Practice – EXAMPLE 1
Q. In the month with the most rainfall,
how much damage did the rain
cause?
A. =VLOOKUP(MAX(B2:B13),B2:D1
3,3)
= \$250,000.00
Microsoft® Excel® Lookup Functions
Some More Practice – EXAMPLE 2
Q. If flooding occurred and the
damages exceeded \$100,000, the
funding. Which months will the
funding.
A. =IF(C2=“Yes”,IF(D2>100000,
“Funds”,“No Funds”), “No Funds”)
OR
=IF(AND(C2=“Yes”,D2>100000),
“Funds”,”No Funds”)
Microsoft® Excel® Lookup Functions
Some More Practice
This next problem is
pretty difficult. The first
person to get it will
Microsoft® Excel® Lookup Functions