reach crc © 2011 reach crc. all rights reserved.fall 2011
TRANSCRIPT
CIS100 Test 2 ReviewREACH CRC
© 2011 REACH CRC. All Rights Reserved.Fall 2011
EXCEL Functions – Test 1
•Mathematical Functions
• SUM• Statistical Functions
• AVERAGE• COUNT• COUNTA• MAX• MIN• Median
Excel Functions- Test 2
•Logical Functions• AND• OR• NOT• IF
•Mathematical Functions
• ROUND• Statistical Functions
• LARGE• SMALL
•Text Manipulation• Concatenate
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 AND function
Microsoft® Excel® Logical Functions
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.
The AND function
Microsoft® Excel® Logical Functions
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
The AND function – EXAMPLE 1
Microsoft® Excel® Logical Functions
The AND function – EXAMPLE 2
Microsoft® Excel® Logical Functions
The OR function
Microsoft® Excel® Logical Functions
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.
The OR function
Microsoft® Excel® Logical Functions
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
The OR function
Microsoft® Excel® Logical Functions
The NOT function
Microsoft® Excel® Logical Functions
Syntax:
=NOT(logical)
Arguments:•logical Required
A value or expression that can be evaluated to TRUE or FALSE.
The NOT function
Microsoft® Excel® Logical Functions
Description:• Reverses the value of its argument.
Remarks:• If logical is FALSE, NOT returns TRUE• if logical is TRUE, NOT returns FALSE
Errors:None
The NOT function
Microsoft® Excel® Logical Functions
The IF function
Microsoft® Excel® Logical Functions
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.
The IF function
Microsoft® Excel® Logical Functions
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).
The IF function
Microsoft® Excel® Logical Functions
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
The IF function
Microsoft® Excel® Logical Functions
value_if_tru
e
[value_if_false]
Nested If in Excel
http://www.fontstuff.com/excel/exltut01.htm
A nested IF statement says something like...
"If the answer is yes, do this. If the answer is no do this or this (depending on...“
Syntax: IF( condition1, value_if_true, IF( condition2, value_if_true, value_if_false ))
Microsoft® Excel® Date Functions
Nested IF Contd.
What was the percentage grade you got on your last test? 75%
You got a C
Cell Address is F42
Nested IF Contd.
What was the percentage grade you got on your last test? 75%
You got a C
=IF(F42<60%,"Sorry, you failed",IF(F42<70%,"You got a D",IF(F42<80%,"You got a C",IF(F42<90%, "You got a B", "WOW you got an A!!!"))))
Cell Address is F42
Microsoft Excel
Mathematical Functions
ROUND=ROUND(number,num_digits)
The ROUND function
Microsoft® Excel® Mathematical Functions
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.
The ROUND function
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
The ROUND function
Microsoft® Excel® Mathematical Functions
=ROUND(-1.475,2) Rounds -1.475 to two decimal places
The ROUND function
Microsoft® Excel® Mathematical Functions
=ROUND(-1.475,2) Rounds -1.475 to two decimal places
The ROUND function
Microsoft® Excel® Mathematical Functions
=ROUND(-1.475,2) Rounds -1.475 to two decimal places
The ROUND function
Microsoft® Excel® Mathematical Functions
=ROUND(-1.475,2) Rounds -1.475 to two decimal places
The ROUND function
Microsoft® Excel® Mathematical Functions
=ROUND(-1.475,2) Rounds -1.475 to two decimal places
Microsoft Excel
Statistical Functions
LARGE=LARGE(array,k)
SMALL=SMALL(array,k)
The LARGE function
Microsoft® Excel® Statistical Functions
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.
The LARGE function
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
=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)
3rd largest number in the numbers in columns A and B
List the numbers in descending order:
7655444332
=5
=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)
7th largest number in the numbers in columns A and B
List the numbers in descending order:
7655444332
=LARGE(array,k)=LARGE(A2:B6,7)
7th largest number in the numbers in columns A and B
List the numbers in descending order:
7655444332
=4
The SMALL function
Microsoft® Excel® Statistical Functions
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.
The SMALL function
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
=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)
4th smallest number in first column
List the numbers in ascending order:
233444567
=SMALL(array,k)=SMALL(A2:A10,4)
4th smallest number in first column
List the numbers in ascending order:
233444567
=4
=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)
2nd smallest number in second column
List the numbers in ascending order:
134788
122354
=SMALL(array,k)=SMALL(B2:B10,2)
2nd smallest number in second column
=3
List the numbers in ascending order:
134788
122354
Microsoft Excel Text Functions
CONCATENATE (including &)=CONCATENATE(text1, [text2], ...)
The CONCATENATE function
Microsoft® Excel® Text Functions
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.
The CONCATENATE function
Microsoft® Excel® Text Functions
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.
The CONCATENATE function
Microsoft® Excel® Text Functions
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
The CONCATENATE function
Microsoft® Excel® Text Functions
A B C
1 Data Data Data
2 Brook trout
Andreas
Hauser
3 species Fourth Pine
4 32
=CONCATENATE(B2, “ “, C2)=Andreas Hauser
The CONCATENATE function
Microsoft® Excel® Text Functions
A B C
1 Data Data Data
2 Brook trout
Andreas
Hauser
3 species Fourth Pine
4 32
=CONCATENATE(C2, “, “ , B2)=Hauser, Andreas
The CONCATENATE function
Microsoft® Excel® Text Functions
A B C
1 Data Data Data
2 Brook trout
Andreas
Hauser
3 species Fourth Pine
4 32
=CONCATENATE(B3, “ & “ , C3)=Fourth & Pine
The CONCATENATE function
Microsoft® Excel® Text Functions
A B C
1 Data Data Data
2 Brook trout
Andreas
Hauser
3 species Fourth Pine
4 32
=B3& “ & “ &C3 =Fourth & Pine
MIS PRACTICE
____________________ enables application software to run on a computer and manages the interaction between the CPU, memory, storage, input/output devices, and other computer components.
MIS PRACTICE
__System software_ enables application software to run on a computer and manages the interaction between the CPU, memory, storage, input/output devices, and other computer components.
Ref. p. 160
MIS PRACTICE
While operating systems are the most prevalent type of system software, other types of system programs include compilers and ____, communications software, and utilities.
A) programmersB) debuggersC) codersD) interpreters
MIS PRACTICE
While operating systems are the most prevalent type of system software, other types of system programs include compilers and ____, communications software, and utilities.
A) programmersB) debuggersC) coders
D) Interpreters Ref: p. 178
EXCEL PRACTICE
(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$2B. =$B$4+$B$4*C2 C. =$B4+$B4*$C2D. =B4*(1+$C$2)E. =B4+B4*C2
EXCEL PRACTICE
EXCEL PRACTICE
Question (Excel) The values TRUE and FALSE are referred to as Boolean values. Answer True False
Question (Excel) Open the attached template file and examine the data closely. What would be the result of the following Excel formula?
=IF(LARGE(D5:D9, 2) < SMALL(C5:C9, 2), "D5", D7)
Answer 55 65 D5 D7 None of the answers provided is correct
EXCEL PRACTICE
Question (Excel) Assume that cell E4 in a spreadsheet has the following formula: =AB$4 + $C44
What formula will result from copying it into cell D8?
Answer =AA$4 + $C48
EXCEL PRACTICE
Question (Excel) Consider the following five values:
-3, 0, 6, 7, and 10
The arithmetic mean of these values is ____ Answer 1 3.94 4 6 None of the answers provided are correct
EXCEL PRACTICE
Question (Excel) What value is returned in a worksheet when the following formula is evaluated?
=OR(25 < 24, MIN(1, 10) < 2, 3 <= 2 + 1)Answer
TRUE FALSE numeric error None of the answers provided are correct
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
What is the result of evaluating the following formulas? (a) =IF(SUM(B4:B7)>=MIN(B6,C5,D4,E7),E4,"Goodbye")
Hello(b) =IF(E6=MAX(C4:C8),SUM(E5:E8),IF(D4>C7,B7,D6))
Coffee(c) =IF(D4=16,SUM(B5:C6)-MAX(C5:D6),MIN(D5:E8))
-25(d) =COUNT(C4:D8)
8(e) =(AVERAGE(B4:B6)-2)/C6
1(f) =IF(B5=SMALL(E5:E8,2),SUM(C4:C8),IF(D4>C7,B7,D6))
Coffee(g) =COUNTIF(B4:C8,">0")+SUMIF(B4:C8,"<0")
8
Question (Excel) In Oldham County, speeders pay a predetermined fine for exceeding the posted speed limit. Using the Excel template provided below, replace the respective 'xxx' cells with the proper formula to respond to each of the three questions that follow. (2 points each)A) What is the formula that should be used in cell E5 to show "No Fine" if the driver was not speeding, but levy a fine of $138 plus $2 for each MPH over the stated speed limit?[PartA]B) What formula should be placed in cell D10 to determine the third slowest speed of any driver in column D?[PartB]
Part A =IF(D5<=C5,"No Fine",138+(D5-C5)*2)
Part B =SMALL(D5:D9,3)
Textbook Question
A compiler checks one statement at a time.
A) TRUEB) FALSE
Textbook Question
A compiler checks one statement at a time.
A) TRUEB) FALSE
Ref: p. 165
Textbook Question
____ made programming somewhat easier because they aggregated common commands into “words,” although many of those “words” are not English-like.
A) Programming languagesB) Utility languagesC) Assembly languagesD) 3GL Languages
Textbook Question
____ made programming somewhat easier because they aggregated common commands into “words,” although many of those “words” are not English-like.
A) Programming languagesB) Utility languages
C) Assembly languages D) 3GL Languages
Ref. p. 160
Textbook Question
Many people refer to sophisticated multimedia applications that run on PCs as ____.
A) polymediaB) hypermediaC) VRD) overmedia
Textbook Question
Many people refer to sophisticated multimedia applications that run on PCs as ____.
A) polymediaB) hypermedia
C) VR D) overmedia
Ref. p. 173
Textbook Question
____________________ applications are programs that enable workers to collaborate in real time over the Web.
Textbook Question
___Groupware___ applications are programs that enable workers to collaborate in real time over the Web.
Ref. p. 171
Textbook Question
Input devices, most commonly computer monitors and printers, deliver information from the computer to a person.
A) TRUEB) FALSE
Textbook Question
Input devices, most commonly computer monitors and printers, deliver information from the computer to a person.
A) TRUE
B) FALSE Ref. p. 122
MIS PRACTICE
Question (MIS, Ch 4) Hardware, in computer terms, refers to the physical components of the computer. Answer
True False
MIS PRACTICE
Question (MIS, Ch 4) Businesses that handle large volumes of retail transactions often store this data in a corporate data center on mainframe computers, which many I.T. professionals fondly refer to as “big iron” in their lingo.
Answer True False
MIS PRACTICE
Question (MIS, Ch 4) Because _____ reduces the amount of paper in organizations, some of its most enthusiastic adopters are companies in paper-intensive fields such as law, retail, insurance, and banking. Answer
processing OMR imaging printing
MIS PRACTICE
Question (MIS, Ch 5) To accelerate their work, application developers can use one of several _____ programming languages, such as Microsoft Visual Basic, Borland Delphi, Micro Focus COBOL, ASNA Visual RPG, and Visual C++.
Answer visual
MIS PRACTICE
Question (MIS, Ch 4) What factors make one computer more powerful than another?
Answer The two major factors to consider are processing speed and memory capacity. A computer’s speed is determined, among other factors, by the CPU clock rate (measured in MHz or GHz), and the amount of information the CPU can process per cycle (determined by the size of the data word and the capacity of internal data communication, which is often referred to as bus width). However, the architecture of the various computer components also plays a significant role in determining processing speed. To mention one, consider the discussion of multi-core CPUs. When two computers are built with the same components except the number of cores, the computer with the greater number of cores is faster.
MIS PRACTICE
Question (MIS, Ch 5) Which of the following refers to sophisticated and interactive multimedia applications that run on more powerful personal computers?
Answer polymedia hypermedia virtual reality temporal reality
MIS PRACTICE
Question (MIS, Ch 5) The most sophisticated virtual reality (VR) devices provide which of the following elements? (select one or more answers)Answer
immersion interaction intersection immobility
Sample Questions
MIS, Ch 4) The data access mode for tape storage devices is:
(A)direct (B) random (C) sequential (D) none of the above
Sample Questions
MIS, Ch 5) Which of the following is considered to be a type of systems software?
(A) compilers (B) communications (C) interpreters
(D) utilities (E) all of the above
Sample Questions
(Excel) An interstate trucking plaza is offering a discount on all diesel pumped based on the total quantity purchased. Which of the formulas below calculates the correct total price to charge the trucker for the diesel they have pumped based on the following conditions:A- no discount (zero dollars off the total amount due) if a trucker purchases less than 50 gallons of dieselB-a 5% discount on all diesel purchased if the person dispenses at least 50 gallons into their tank
Sample Questions
Assume that cell A1 shows the total quantity (in gallons) of fuel purchased and cell B1 is the price per gallon. Provide a formula that will correctly calculate the total amount that a person should pay, after the discount (if any) has been applied. =IF(A1<50, A1*B1, A1*B1*95%) and then apply some algebra to get:
=A1*B1*IF(A1<50, 1, .95) note the different format (no percent)
Sample Questions
(MIS, Ch 5) Since Microsoft does not make the source code for Internet Explorer available for end users to review and update, the product is considered proprietary.
True False
THANK YOU FOR ATTENDING CIS TEST REVIEW SESSION