CIS FINAL
(Excel) Match the following types of error messages that may occur when working with formulas in a worksheet with their respective descriptions.
#NAME?=Unrecognized text in a formula #REF! =Invalid cell reference #VALUE! =Wrong argument type or operand #NUM! =Invalid numeric values in a formula or function
(Excel) You are looking for a new home for your family, and you narrow your choices down to two towns in Washington State with various criteria as detailed in the image below. To help with your decision, you want to compare the houses in each town based on different criteria as noted below. Using relative cell references only, provide formulas which will determine each of the following, assuming that your data may change, but the number of real estate listings will not. (2 points each) (A) How many homes located in Issaquah have at least 2 bedrooms but no garage? [PartA] (B) What is the total listing prices for all homes located in Bellevue that have at least 3 bedrooms? [PartB] (C) What is the average price of homes located in Issaquah that are priced over $300,000? [PartC
(A) =COUNTIFS(C2:C7,"Issaquah",D2:D7,">=2",E2:E7,"No") (B)=SUMIFS(B2:B7,C2:C7,"Bellevue",D2:D7,">=3") (C)=AVERAGEIFS(B2:B7,C2:C7,"Issaquah",B2:B7,">300000")
(Excel) Answer the following date-related questions. (2 points each) (A) What is the formula to determine the number of days until Independence Day (July 4th) of 2020? [PartA] (B) John was born on January 5, 1989. What is the formula to determine how many years old John is? [PartB]
(A) =DATE(2020,7,4) - TODAY() (B)=YEARFRAC(DATE(1989,1,5),TODAY())
(Excel) Read each of the following questions and provide a response for the first two using a properly formatted day of the week or date, respectively, and then a whole number for the latter two. (1 point each) A) What day of the week did the date represented serial number 12345 in a given worksheet? [PartA] B) What is the date that is 999 days after September 19th of the year 1999? [PartB] C) What is the number of days that Franklin D. Roosevelt served as President of the United States? Hint: FDR was inaugurated on March 4, 1933 and then died in office on April 12, 1945.? [PartC] D) Which undocumented function should be used to return an integer value to determine the age of any person given their birth date? [PartD]
(A) Wednesday (B) 6/14/2002 (C) 4422 (D)DATEDIF
(Excel) Download the attached template file which contains U.S. Presidential data to the PC Desktop and then open it in Excel. Use one or more of the functions, calculations, and/or features that you have learned thus far in CIS300 to answer each of the three questions below. (1 point each) Note: Each of your answers must be rounded to the nearest whole number. A) What is the average age for all U.S. presidents whose Religious Affiliation is designated as Quaker? [PartA] B) What is the average salary of all U.S. presidents who were not affiliated with either the Federalist or Whig parties? [PartB] C) How many U.S. Presidents between the ages of 45 and 59 were affiliated with the Episcopalian religion? [PartC]
(A)55 (B)$92,763 (C)7
(Excel) Review the accompanying worksheet image and then answer the questions below. The first row and column in the worksheet refer to worksheet column and row labels, respectively. The worksheet shows the annual salaries for several employees in a company. Provide responses for the following three questions. (2 points each) A) Using mixed cell references only, what is the formula to determine the total number of employees who earn more than $55,000 in annual salary? [PartA] B) Using absolute cell references only, what is the formula to determine the sum of the salaries of employees who earn above the average annual salary for this group of employees? [PartB] C) Using relative cell references only, what is the formula to determine the average salary of all employees who earn less than $45,000? [PartC]
(A)=COUNTIF($C4:$C14,">55000") (B)=SUMIF($C$4:$C$14,">"&AVERAGE($C$4:$C$14)) (C)=AVERAGEIF(C4:C14,"<45000")
(Excel) You recently won a $250,000 prize in one of the Kentucky Lottery Scratch-Off games. You decide to invest the money in a safe investment that earns 8% per year for the next 20 years while you continue to work. At the end of each year for the next 20 years, you withdraw $15,000 from the investment to spend on yourself and your family. Using the information answer the following: (3 points each) A. Write a formula that will calculate the amount of money you will have at the end of 20 years given the conditions above. [PartA] B. What is the amount of money you calculated for Part A? [PartB]
(A)=FV(8%,20,15000,-250000,0) (B)$478,809.82
(Excel) Review the spreadsheet image which shows names for property owners and the respective value of their residence. (1 point each) A) Using mixed cell references only, what formula containing one unique function should be used in cell A16 to determine the middle value of prices for all homes listed? [PartA] B) Using absolute cell references only, what formula containing one unique function should be used in cell A17 to round the result in cell A16 to the nearest thousand dollars? [PartB]
(A)=MEDIAN($A3:$A15) (B)=ROUND($A$16,-3)
(Excel) A recent College of Business graduate landed an amazing job right out of college and allows her to save $2,000 per month because she is a bit more frugal than her boyfriend who tends to foolishly spend his money. She does not have any savings right now because she has had to pay for college. However, her goal is to have saved one million dollars ($1,000,000) by the end of ten (10) years at a rate of 0.8% on her money each month. A. Write a formula that will calculate how much money she should use as an opening deposit for a savings account now to help her reach her goal? [PartA] B. What is the value of your formula's result in Part A? [PartB]
(A)=PV(0.8%*12*10,10,-2000,1000000,0) (B)$885.65
(Excel) Provide a formula in each of the answer spaces below that will satisfy the following three conditions, respectively. Do not use any cell references. (1 point each) A) Round a water bill calculation that results in the value 42.7357 to the nearest penny. [PartA] B) Round the value 65.18% to the number 0.652 precisely. [PartB]
(A)=ROUND(42.7357,2) (B)=ROUND(65.18%,3)
(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the worksheet refer to Excel column and row labels, respectively. DeepSix Legal Services has offices in Chicago, Louisville, and Nashville. In the worksheet above the Employee List lists the employees, their office locations, and their job titles. In addition, the Bonus Lookup Table shows that the annual bonus awarded to an employee is based on the location and the job title of the employee. For example, each Manager in Chicago gets a bonus of $10,000, each Staff employee in Louisville gets a bonus of $5,000, and each Clerical employee in Nashville gets a bonus of $2,000. (A) What VLOOKUP formula may be entered in cell E10 to determine the bonus for Allisa, the first employee listed in the Employee List? (Note: Your formula will need to work properly for all other employees when copied down though cell E24.) [PartA] (B) What HLOOKUP formula may be entered in cell E10 to determine the bonus for Allisa, the first employee listed in the Employee List? (Note: Your formula will need to work properly for all other employees when copied down though cell E24.) [PartB]
(A)=VLOOKUP(C10,$B$4:$E$6,IF(D10="Manager",2,IF(D10="Staff",3,4)),FALSE) (B)=HLOOKUP(D10,$C$3:$E$6,IF(C10="Chicago",2,IF(C10="Louisville",3,4)),FALSE)
(Computer Security) Which of the following are common examples of clever schemes created by con artists to scam millions of people around the world into parting with their money or sensitive personal information?
--Overseas eCommerce sites asking you to wire transfer of funds to them for payment purposes --Unfamiliar charities calling you at home shortly after a major natural disaster strikes --Emails and text pages requesting your financial information --Postal mail informing you that you are the latest winner of a foreign lottery
(Computer Security) Which of the following are common examples of clever schemes created by con artists to scam millions of people around the world into parting with their money or sensitive personal information?
--Postal mail informing you that you are the latest winner of a foreign lottery --Emails and text pages requesting your financial information --Unfamiliar charities calling you at home shortly after a major natural disaster strikes --Overseas eCommerce sites asking you to wire transfer of funds to them for payment purposes--
(IIS, Ch 06) Many organizations have implemented corporate portals for which of the following reasons?
--reduce costs --free-up time for busy managers --increase profitability --offer customers self-service opportunities
(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. Evaluate the function below to determine what value will result. =HLOOKUP(SMALL(C9:D10,3), $B$3:$G$27, 3, FALSE)
18
(Excel) How many total cells are included in the following formula, including all of the arrays (or cell ranges) that are being referenced: =MIN(COUNT(A7:F8),MAX(B4:C6,O1),SUM(B9:D9))
22
(Excel) How many commas are contained within a single function that has four arguments within the set of accompanying parenthesis?
3
(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. Evaluate the function below to determine what value will result. =VLOOKUP(42, $B$3:$G$20, 3, FALSE)
44
(Excel) How many unique cells are included in the array (or range) that is being referenced in the following formula? =SUM(B19:F24,D4,A3:B11,K9)
50
(Excel) Assume that cell C8 in a worksheet contains the following formula: =$G$4+MAX($D10:F15)*E$6 What formula will result from copying it into cell A5?
=$G$4+MAX($D7:D12)*C$6
(Excel) A local tire store is offering 20% off on a set of four radial tires that cost $125 each (including balancing, installation, and disposal fee). Provide a formula in the answer space below to calculate the total amount that the customer will have to pay for a set of these new tires, including a sales tax of 6% on the sub-total.
=(4*125-4*125*20%)*1.06
Chloe is creating an Excel spreadsheet using a number of formulas to help make some business decisions regarding her company's customers. Please refer to the figure above as you answer the question below. Chloe also intends to send out a letter congratulating those valued customers who have the value TRUE in all three columns of C, D, and E. She first wants to write a simple formula to generate a TRUE if a customer matches this criteria, demonstrating their strong creditworthiness. IF a customer doesn't meet her high credit expectations, then a FALSE should be displayed. Which of the following Boolean logical constructs should Chloe use as a formula in cell F5, then copied down that column to correctly determine whether a letter should be sent (or not) to all of the customers on her list?
=AND(C5:E5)
(Excel) Closely examine the accompanying spreadsheet image which shows property sales for the month of October. What formula should be entered into cell G3 and copied down the entire column to properly calculate the amount of commission earned for any of the residential properties that are listed?
=D3*B$13
(Excel) Enter a formula to determine how much money you will have in your IRA investment account 40 years from now if you deposit $2,000 today and gains interest at a rate of 6% annually.
=FV(6%,40,0,-2000,0)
(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. The Price Table in the accompanying worksheet image lists the unit price of four (4) items sold by a company. What formula should be entered in cell C7 to determine the unit price of any item entered in cell C6?
=HLOOKUP(C6,C10:F11,2,FALSE)
(Excel) A tollway truck plaza gives a discount on diesel fuel to truckers based on the total quantity they purchase. Which of the formulas below calculates the correct total price to charge a customer for the diesel pumped based on the following conditions: no discount (zero dollars off the total due) if the trucker purchases less than 50 gallons of diesel a 2% discount on all diesel purchased if the trucker buys at least 50 gallons of fuel Assume that cell A1 shows the total quantity (in gallons) of diesel purchased and cell B1 is the price per gallon. The correct formula will calculate the total amount that the trucker should pay, after discount (if any) has been applied.
=IF(A1>=50,A1*B1*98%,A1*B1)
(Excel) A customer applies for a loan and the bank reviewing the application uses the following rules: If the applicant's FICO score (entered in cell A5) is less than 650, then the application is rejected. However, if the applicant's FICO score is greater than 750, then the applicant is approved and the interest rate is set at 5.5%. Otherwise, the interest rate is set at 6.5%. Which of the following formulas will give the correct answer?
=IF(A5 < 650, "Rejected", IF(A5 > 750, 5.5%, 6.5%))
Nathan is in charge of Collections, and is putting together a list of customers with poor credit to call. Please refer to the figure above as you answer the question below. Nathan wants to fill in the words "Call" and "Don't Call" on the worksheet in the adjacent column D based on the value shown in column C. He will list "Call" if the value is FALSE because the customer has a poor credit rating. Otherwise, the display will show "Don't Call" if the value is TRUE. Which of the following pairs of formulas could Nathan write in cell D5 (and then copy down the column) to properly show this?
=IF(C5=NOT(TRUE), "Call", "Don't Call") =IF(C5, "Don't Call", "Call")
(Excel) Enter a formula to determine the number of months a new company could last if it raised $4,500,000 and had a burn rate (spending rate) of $75,000 per month, assuming it kept its cash in an account earning 6% per year.
=NPER(6%/12,75000,-4500000,0,0)
(Excel) Enter a formula to determine the monthly payment on a 20-year fixed rate residential mortgage with an initial balance of $190,000 at 6.5% per year.
=PMT(6.5%/12,20*12,-190000,0,0)
(Excel) Enter a formula to determine the amount of money you would need to accumulate to be able to retire for the rest of your life on $70,000 per year if your money earns 8% per year and you expect to live for another 45 years.
=PV(8%,45,-70000)
(Excel) Enter a formula to determine the monthly interest rate you are paying to your kind professor if you borrow $6,000 today and pay back $350 each month for 23 months and then pay back $6,000 two years from now.
=RATE(24,-350,6000,-6000,0)
(Excel) Enter a formula to determine the monthly interest rate you are paying to Joe the Shark if you borrow $6,000 today and pay back $500 each month for 23 months and then pay back $6,000 two years from now.
=RATE(24,-500,6000,-6000,0)
Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the worksheet refer to Excel column and row labels, respectively. In the worksheet below, cell B2 contains the date January 1, 2008. What is the formula to determine the number of days that have passed since January 1, 2008?
=TODAY()-B2
(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the worksheet refer to Excel column and row labels, respectively. At the Louisville Resort Camp, each child is assigned to a specific group based on age according to the Group Table shown in the worksheet above. For example, children ages 2 or 3 years old are assigned to the Jaguar Group; children who are at least 6 years but no older than 9 years old are assigned to the Tigers group. However, teenagers (i.e., children 13 years and older) are not assigned to a group; instead, they are assigned to "None". Which of the following formulas, when entered into cell D12, will determine the correct group to assign each child? (Note: Your formula will need to work properly when copied down though cell D20.)
=VLOOKUP($C12, $B$4:$D$8, 3)
(Excel) Review the accompanying worksheet image and then answer the question below. The first row and column in the table refer to Excel column and row labels, respectively. The worksheet contains a list of students and each student's final score (out of 100 possible) in the course. It also contains a Grade Lookup Table which shows how the professor will assign letter grades to students in her course. For example, students earning a score less than 40 will receive an F; students earning a score higher than or equal to 40 but less than 70 will receive a D.
=VLOOKUP(C4,$F$4:$G$8,2)
(IIS, Ch 06) A protocol is:
A standard set of rules and procedures for the control of communications in a network.
(Excel) Carefully review the accompanying worksheet image. Based on the information provided, what appears to be the active cell in this worksheet?
A1
The White House's overriding concern with Healthcare.gov was __________
C. whether people would visit the exchange
(IIS, Ch 04) Which of the following is a prime example of a SCADA attack?
Computer viruses are introduced into the electrical company's systems resulting in a shutdown
(Excel) Closely review the accompanying worksheet image. What is the result of evaluating the following formula? =IF(AVERAGE(E5:E7)<=MIN(B5:C7),D7,"D4")
D4
(Computer Security) When an employee is terminated, system administrators should receive notification in advance so that they can do which of the following?
Disable related user ID accounts and change administrator passwords
(IIS, Ch 06) Which of the following is a bundled Internet access, telephone, and television service offered by Verizon that operates over a fiber-optic communications network?
FiOS
(Access) Craig is working in a database that stores homes for sale for a Realtor. The database contains a table called HomeListings which contains a field called ListingDate that holds the date that a home was listed. Craig is designing a query that will identify houses on the market for less than 30 days as new listings, and houses on the market 30 days or more as old listings. This information will be included in a query column called ListingAge. Which of the following expressions will achieve these results?
IIf(Date-[HomeListings]![ListingDate]<30,"New Listing","Old Listing")
(IIS, Ch 06) Bluetooth is considered to be within which of the following network classifications?
PAN
(IIS, Ch 6) What is the most important and pervasive set of protocols that enable computers to communicate with each other over the Internet?
TCP/IP
(IIS, Ch 04) Which of the following is an encryption standard used for secure transactions such as credit card processing and online banking?
TLS
The ____________________ function returns the current date's serial number (based on the computer's internal clock)
TODAY
(Access) Ben is working in a database that stores student information. He wants to determine each student's age based on the student's birthday. Which of the following statements accurately describe how the fields should be designed?
The birthday should be a stored field and the age should be a calculated field.
(Access) A foreign key in a table must have a corresponding primary key found in another table of the same database.
True
The ____ function calculates the number of years between two dates.
YEARFRAC
The ____________________ function calculates the number of years between the two dates.
YEARFRAC
(Extra Credit) Briefly summarize the article posted in Course Modules 2.5 about Yahoo! CEO Marissa Mayer and a controversial decision that she made about a year ago regarding a technology-assisted work approach.
Yahoo! CEO Marissa Mayer was at a conference in April 2013 where she finally decided to address what she called "the [purple] elephant in the room" and talk about why she had banned telecommuting at the company some two months previously. She stated that while it was true that employees tended to be more productive when they worked from home, they also missed out on the interaction and collaboration that is afforded when working in a common physical environment. To futher illustrate her point, she mentioned improvements made to the weather app for iOS by two programmers at Yahoo!. These two individuals both came up with an idea to have the background of an iOS weather app show the weather in your area based on geolocation technology. As she pointed out, this collaboration would not have occured had these two individuals not been working side-by-side in a common setting. One week after the Yahoo announcement, Best Buy said it would end its flexible work policy as well.
$B$1 is an example of a(n) ____ reference.
absolute
(IIS, Ch 06) Which of the following is not a component of a local area network?
bridge
(Access) Closely examine the accompanying table image. Which of the following fields would be the best to use for the primary key?
client ID
(IIS, Ch 04) Which of the following is a prime example of a SCADA attack?
computer viruses are introduced into the electrical company's systems resulting in a shutdown
(IIS, Ch 11) A check-in kiosk at an airport is an example of what type of CRM application?
customer touching
(IIS, Ch 04) Which of the following types of remote software attacks does not require any action by an end user?
denial of service
The Healthcare.gov website was implemented with a _________ conversion approach, but most e-commerce web sites use a _________ conversion approach to monitor how the service functions, fix it if necessary, and then scale it.
direct; pilot
Computer Security) When an employee is terminated, system administrators should receive notification in advance so that they can do which of the following?
disable related user ID accounts and change administrator passwords
(Computer Security) Organizations should secure their sensitive data by storing it in _______ form?
encrypted
(Access) A form can be used to:
enter, edit, and view records in a database
(IIS, Ch 04) Low level employees pose the greatest threat to information security within an organization.
false
(IIS, Ch 04) The emergence of the World Wide Web has significantly decreased the threats to information security.
false
(IIS, Ch 06) Digital signals have two parameters, namely: amplitude and frequency.
false
(Access) Closely examine the accompanying table image. The column labeled Last Name is an example of which of the following?
field
$B1 is an example of a(n) ____ reference.
mixed
(Access) A primary key:
must contain a unique value for each record within the table
Healthcare.gov is a type of ______________ CRM system.
operational, customer-touching
(IIS, Ch 6) Which of the following types of networking media is the least susceptible to electromagnetic interference (EMI) and provides some of the fastest data transmission speeds possible?
optical fiber (OC)
(IIS, Ch 6) Which of the following is defined as a group of bits transmitted together over a network?
packet
(Access) A field, or a combination of fields, that has a unique value is a:
primary key
One of the main reasons the Healthcare.gov launch was such a failure was the lack of involvement of Obama's team in the _________ stage of the systems development life cycle.
programming and testing
(Access) A ________ is a question you ask about data stored in a database.
query
(Access) Closely examine the accompanying table image. The last row of data shown is known as which of the following?
record
(Access) A relational database is a collection of:
related tables
(IIS, Ch 04) Which of the following is not a common risk mitigation strategy?
risk analysis
(IIS, Ch 04) Which of the following types of alien software uses your personal computer to send email messages that appear to have come from you using the contact list in your address book?
spamware
(IIS, Ch 06) Referring to the chapter's Opening Case - The Network Neutrality Wars, which of the following is not a challenge for achieving net neutrality?
telecommunications and cable companies are in favor of net neutrality
(Extra Credit) Following its impressive win against two past Jeopardy! champions, the IBM Watson computing platform was applied to solving some complex challenges in both the healthcare and financial services industries.
true
(IIS, Ch 04) If you have copied a proprietary software package (computer application program) from a friend or your business without paying for it, then you are guilty of software piracy.
true
(IIS, Ch 04) Risk management identifies, controls, and minimizes the impact of threats to the organization's information security.
true
(IIS, Ch 06) Affinity portals support communities such as specific hobby groups or a given political party.
true
(IIS, Ch 06) Analog signals have two parameters, namely: amplitude and frequency.
true
(IIS, Ch 06) Extranets offer limited accessibility to the intranets of participating companies.
true
(IIS, Ch 06) File sharing is a type of peer-to-peer (P2P) network processing.
true
(IIS, Ch 06) The top-level domain (TLD) is always found at the end of the domain name in a URL.
true
The TODAY function returns the current date's serial number (based on the computer's internal clock).
true
The result of the formula =B3>=C3 where cell B3 contains the date 1/1/2010 and cell C3 contains the date 12/31/2009 is ____.
true
The syntax for the YEARFRAC function is as follows: =YEARFRAC(start_date,end_date,basis).
true
