MIS 112 Problem Set 7/8
Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet.
#REF!
Please see the attached workbook. If the formula =MATCH(B2,I4:I7,0) was used in cell B3, what would be the result?
2
Please see the attached workbook. The formula =LOOKUP("Mary",A2:A5,C2:C5) returns what value?
28
Please see the attached workbook. What value of tax rate is returned when the entered income is $22,566?
28%
Please see the attached workbook. If you have to use a formula to retrieve the age of the student from the given array of data, which of the following formulas will you use?
=INDEX(J4:L8,3,3)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 is an odd number?
=ISODD(A1)
#NAME? error occurs under which of the following conditions?
All of the options provided are correct
How can you avoid the "#NAME?" error value?
All of the options provided are correct
How are some icons hidden in Icon Set conditional formatting?
By choosing "No Cell Icon" in the Edit the Rule Description box.
What is NOT true about Autocorrect options?
Changes made by the Autocorrect feature cannot be undone
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length?
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a time less than a specified time?
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time.' In the Data box, select 'less than' and enter the end time.
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a date greater than a specified date?
Click the Settings tab (in the Data Validation box). In the Allow box, select 'date.' In the Data box, select 'greater than,' and specify the start date.
Which of the following is NOT one of the buttons in the Spelling dialog box in Excel?
Conditional Formatting
When entering/editing an Array formula, press _______ to enter the formula (or Excel does not recognize the formula as an Array formula).
Ctrl + Shift + Enter
From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a date such that it falls on a Sunday?
Custom
Please see the attached workbook. What format option is used in cell C6 to make the value stand out?
Custom (combination of rules) format
How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated?
Data(tab)-> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning
In order to delete information using Find and Replace in Excel, enter the text to be deleted in the _____ field and leave the Replace With field _____.
Find What, empty
Light, Medium and Dark are the categories of styles found under the ______ command [within the Home (tab) -> Styles (command group)].
Format as Table
The HLOOKUP and LOOKUP functions can be found under Formulas (tab) -> _________.
Function Library (command group) -> Lookup & Reference
If you want to find the cells on the worksheet that have data validation, which option would you select?
Home (tab) -> Editing (group) -> Find & Select -> Data Validation
Which function returns the contents of a cell at the intersection of a specified row and column from a range?
INDEX ()
When (or why) does Excel draw arrows to indicate an error source?
In Tracing Error Values
Please see the attached workbook, Sheet3. When you print the worksheet with graphic background what happens?
No background is printed, even though the background is displayed onscreen.
If you want to trace cell relationships for formulas, which of the following commands/options could you use?
None of the options provided is correct
Please see the attached workbook. Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden?
Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.
Which of the following options is TRUE about the different lookup functions?
The CHOOSE() function returns a specific value, corresponding to the index number, from a supplied list of values.
Please see the attached workbook. To round off B11 cell to four decimal places what formula would you use?
The formula: =ROUND(B11, 4)
Please see the attached workbook. What does '3' in the expression =VLOOKUP("John",A2:D13,3,FALSE) represent?
Third column
Which rule category does the following conditional formatting rule belong to? "Highlight the top 10 items"
Top/Bottom rules
_______ lookup functions will look up a value in the first column of the lookup table (or range) and return a corresponding value from the specified table column (within the same row).
Vertical
Which of the following BEST applies to the INDEX function?
You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)
Consider the HLOOKUP function in Excel. If the "range_lookup" argument is set to TRUE, we need to sort the first column of the range in ____________ order
ascending
What is the use of the following data validation formula: =ISTEXT(A1)?
forces the cell A1 to accept only text
Using VLOOKUP in some ways is similar to using a(n) _______
phonebook
Which of the following is NOT one of the VLOOKUP function arguments?
row_index_num
The HLOOKUP function searches for data in ______.
rows
Please see the attached workbook. The formula in B1 which is copied down the column is "=TRIM(A1)=A1." The formula returns false for (B3) because the text in that cell contains?
trailing space
Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.
visualize, trends
