MIS 111L
Which operator in Excel combines multiple range references into a single reference (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)?
"," (comma)
The lookup value can include wildcard characters if the lookup_value argument is ______ and range_lookup argument is ________
"text, false"
Please see the attached workbook. Using the formula =B2/C2 what error is displayed?
#DIV/0!
The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?
#N/A
Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected.
#NUM!
Step 3 of the Query Wizard lets you specify how you want the ______.
**** find answer
Examine the attached image. What will the result of the following formula be? ="Section " &mysection
***** find answer
In Excel when calculating loan information, what does the PV() function return?
***** find answer
How do you copy a Pivot table (including its design format) to a new worksheet?
*None of the answers provided is valid* select Insert (tab) -> Tables (group) -> Pivot Table select Insert (tab) -> Copy Pivot table. Then select a new worksheet and press Ctrl +V select the entire table and choose Home (tab) -> Clipboard (group) -> Copy (or, press Ctrl+C). Then select a new worksheet and choose Home (tab) -> Clipboard (group) -> Paste -> Paste Values
Examine the attached workbook. If you format a chart element with the font seen in Chart 2, how can you reset it to its original state?
*look up*
By default, what is the extension used when you save a workbook in Excel 2016?
.xlsx
Please see the attached workbook. How many names does the workbook have defined? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS3_files/rng_range_names.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
2
With a single IF() function, how many actions can you ask Excel to carry out?
2 (i.e., an action if the criteria is true, or an an action if the criteria is false)
In Excel, the maximum length of a Web query URL is _______ characters.
255
In Excel, the DATE function takes _____ arguments.
3
The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text
3
A single cell in Excel can hold up to approximately ________________ characters.
32000
What is the result of the following expressions (formulas), respectively? = (10 - 6) * 10 and = 10 - 6 * 10
40 and -50
What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000?
4500
What does the following formula return? =SEARCH("m","Big Mama Thornton",1)
5
What does the following formula return? =FIND("m","Big Mama Thornton",1)
7
If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format)
9/20/2014 1:00:00am
To display today's date in the format like "Today is Friday April 4, 2014", which formula should you use?
="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")
Examine the attached worksheet. Which of the following formulas, when entered in cell E6, will give the Discount percentage for Pick Axe? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/Formulas_Test.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
=(1-(F6/D6))*100
To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple) https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS1_files/PS1_formulas.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
=C20/F8
In the Student Rank table, notice that H15 and H19 have the same value. Since two students cannot have the same student ID, what formula would you use in the Data validation settings in this case to make sure duplicate entries are not allowed?
=COUNTIF($H$13:$H$19,H19)=1
Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters?
=COUNTIF(B2:B23,"??????")
Which formula can be used to find: How many songs were sung by singers whose names begin with S? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS4_files/Advanced_aggregation.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
=COUNTIF(B2:B23,"S*")
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive?
=HYPERLINK("C:\TEST\example.xslx",B5)
Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4?
=HYPERLINK("[hyperlink_examples.xlsx]A4",A4)
You may use the attached spreadsheet, "IF_formulas.xlsx" to answering this question. Which of the following IF() functions evaluate as being TRUE?
=IF(B10>100,"true","false")
Which of the following formulas, when entered in cell B3, will give you the CURRENCY of the country CHAD? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/Country.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
=INDEX(I2:I239, MATCH("CHAD", D2:D239, 0))
Please see the attached workbook. If you have to lookup the supplier of the item C43 in the given array of data, which of the following formulas will you use? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS7_files/dataLookup_eg4.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
=LOOKUP(B1,H2:H7,I2:I7)
Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS1_files/PS1_formulas.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
=SUM(C4:C7)
What changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match?
=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)
Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?
?, *
Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?
?NULL?
Which of the following statements is true?
A workbook can have many worksheets.
You are reviewing a Spreadsheet that records daily sales for Burgers, Hotdogs and Chili. Monday of last week shows exceptionally high sales for Burgers. You want to email the spreadsheet back to your assistant so he can look into whether the data was correctly entered.
Add a comment to the sales figure cell, and explain to your assistant what you want him to do
The SUM() function does which of the following in Excel?
Allows you to total up numbers in a specified range of cells
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()?
Both A and B Computes depreciation at a fixed rate. Its arguments are: Cost, Salvage, Life, Period, [Month]
How are some icons hidden in Icon Set conditional formatting?
By choosing "No Cell Icon" in the Edit the Rule Description box.
Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?"
COUNT()
To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:
Click anywhere on the table
Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct?
Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button
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.
What does the following formula =A1=C12 do?
Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE
What option in the Table Tools / Design tab removes a table without losing the data in it?
Convert to Range
Which keyboard shortcut is used to select all cells in an open Excel worksheet?
Ctrl + A
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?
Ctrl + N
Which rule category under Conditional formatting can be used if you want to apply graphic bars directly in the cells, proportional to the cell's value?
Data Bars
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
Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?
Developer
When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Pages] ?
Displays the total number of pages to be printed
Which of the following does the Formula Autocomplete feature in Excel take into account?
Excel built-in functions user-defined functions defined names
Which of the following is NOT true when it comes to adding new worksheets within an existing Excel workbook?
Excel does not allow you to add additional worksheets to a workbook that has been saved previously
Which of the following command sequences will allow you to save a copy of your workbook with a different name?
File, Save as
How do you change the calculation mode while dealing with a calculation intensive data table?
Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables
The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data (tab) -> Get External Data (command group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that has a value 'Tucson' for 'City' column.
From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' from the second drop-down list
The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). Another way to insert a function is to use the ______ on the _________ tab.
Function Library group (on the) Formulas
Which function returns the contents of a cell at the intersection of a specified row and column from a range?
INDEX()
Which of the following function(s) can be used to count error values in a range?
ISERROR() ISERR() ISNA()
How can you avoid the "#NAME?" error value?
If the formula uses a label in a formula, make sure labels are allowed Enclose any text in double quotation marks Make sure all range references in the formula use a colon
In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?
Input Range and Bin Range
If you need to display lots of text in a worksheet, you can consider using a text box. How will you insert a textbox?
Insert (tab) -> Text (command group) -> Text Box
After you have selected the data to create a chart, which of the following is the correct sequence of options needed to create a clustered column chart?
Insert --> Charts --> Column --> Clustered Column
The basic formula for computing simple interest is:
Interest = Principal * Rate * Term
Please see the attached Excel workbook. Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background?
It adds a background image to the currently active worksheet
In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting
Manage Rules
How do you remove a filter in the PivotTable Field List?
Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.
Which of the following is NOT an advantage of using defined range names to work with ranges in Excel?
Moving to areas of the worksheet is controlled
If you want to trace cell relationships for formulas, which of the following commands / options could you use?
Name manager Spelling Both options A and B *None of the options provided is correct*
What command is used to *create your own rule* in conditional formatting?
New Rule
Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell?
No
Please examine the attached workbook. The chart was created based on data in cells A1 through C6. If new data is added in cells A7 through C7, will the chart include the new data automatically? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/PS2_Chart1.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
No
Which of the following is NOT one of the four quick margin settings for adjusting margins?
No margins
Please refer to the attached image. What is the result upon using the formula =B2-C2? B2: 2/3/2014 C2: 1/11/2014
Number of days between two dates
What happens if the following value filter is added to the existing report? Value filter: Sum of Sales is greater than 5000.
Only one record corresponding to the 'CA' state remains as its total sales amount is greater than 5000.
What is the correct syntax for the PMT function, if we are only considering the required parameters/arguments for the function? (Note: "nper" means Number of Payments)
PMT(rate,nper,pv)
Which of the following is NOT one of the methods for 'Conditional Formatting' in Excel?
Pie Chart
Which of the following is an object that contains the actual data in the chart?
Plot area
By default, Excel prints worksheets in ___________ orientation
Portrait
What does the following formula do? =COUNTIF(Data, TODAY())
Returns the number of cells containing the current date in the range named Data.
Adding comments to a cell is available through which tab of the Excel ribbon?
Review
What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?
Selects a rectangular range of cells around the active cell
To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.
Shift+F2
To create a single file web page, what file format does a user have to select from the Save as Type drop-down list?
Single File Web Page
______ is an interactive control that makes it easy to filter data in a pivot table.
Slicer
When importing a text file into Excel, the _____________ helps specify how the data will be imported.
Text Import Wizard
Excel formatting tools are available at the following locations:
The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar
A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?
The formula returns an error.
The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted?
The word "budget" can appear anywhere within the cell.
There are four boxes/areas below the PivotTable Field List. Which of the following is NOT one of these four boxes?
Tools
Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE?
Two criteria are checked for this formula to include a value in the total: a) the product category must be "Beverages", and b) the supplier must be "4"
Which of the following is an advantage of using Excel tables?
Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas
How can a manual Page Break be removed from a worksheet?
Using the: Reset All Page Breaks command
What of the following is FALSE about filtering a table?
When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.
Which of the following file types can Excel import?
XML Files (.xml) Comma separated values (.csv) Microsoft Access (.accdb)
You can use a data Form to add, find, and edit rows in a range or table. Is this correct?
Yes, all these are valid form operations
Please see the attached workbook. If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2? https://d2l.arizona.edu/content/enforced/541535-470-2171-1MIS111L001/csfiles/home_dir/problemsets/PS2_files/cells_sales.xlsx?_&d2lSessionVal=nUDalnMQATUPovdEjGTjphbXq
Yes, the formula range will be adjusted automatically
When you try to import data from MS-Access into Excel, which of the following is TRUE?
You can import a table or a query
Which of the following is TRUE about Pivot tables?
You cannot add new rows or columns to the Pivot table You cannot change any of the calculated values You cannot enter formulas within the pivot table *All of the answers provided are TRUE*
Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true?
You cannot replace a type of formatting in a cell with another type of formatting.
Which of the following is TRUE about the DATEDIF() function?
You must enter it manually
Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote?
a comment exists for that cell
Which of the following is the difference between a Pie and Doughnut chart in Excel?
a doughnut chart can display more than one series of data
In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.
a text string
To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria?
a tilde followed by an asterisk
When a range of cells is selected, which of the following occurs?
all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded
A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other _______.
also changes
By default, Page Breaks __________ displayed in the Normal view of worksheet.
are not
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
A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?
column
A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table.
column, row, filter
In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following?
content other than text (e.g., a date or a number) or a blank cell
Which of the following does the function =NOW() return?
current date and time
A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________
delete a record/row
Which of the following is NOT a type of cell reference?
dynamic
What does the Excel keyboard shortcut Ctrl+Alt+F9 do?
forces a complete recalculation of all formulas in all open workbooks.
When the value of a cell is changed (after conditional formatting is applied), the cell formatting ______ based on the conditions set.
is updated
Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.
letter, number
For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?
number of cells containing the current date
A chart in an Excel worksheet provides a visual representation of which of the following?
numeric values
You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?
passwords are case sensitive
Data retrieved from Web Queries do not include __________ and contents of __________.
pictures, scripts
Which of the following methods will NOT move or copy a worksheet?
press keyboard shortcuts Ctrl + C and Ctrl + V in order
A group of cells is referred to as which of the following?
range
Fill in the blank: The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.
range (and) criteria
Which of the following is NOT one of the VLOOKUP function arguments?
row_index_num
The HLOOKUP function searches for data in ______.
rows
In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______.
select a column
Highlight Cell Rules and Top/Bottom Rules use a single type of formatting to highlight ________ cell(s) based on the condition or requirement.
specific
What best describes the connection between a Pivot Table and a Pivot Chart?
two-way link (updates made in the Pivot Chart are reflected in the PivotTable)
Which of the following methods will keep a heading at the top of a table visible while you scroll through rows lower down in the worksheet?
use Freeze Panes
