MIS 112 Midterm

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following? #N/A #REF! #VALUE! #NULL!

#N/A

Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers? 12 8 10 14

12

worksheet_file1.xlsxPlease refer to the attached workbook. What is the width of Column D? 23 35 27 45

45

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 500000 4750 6750

4500

What would you type in the Find and Replace dialog box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2? 5?2 5??2 52? *2

5??2

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet? = NOW() = DATE() = TIME() = TODAY()

= NOW()

Which of the following shows the correct syntax for referencing cell "A2" in another worksheet? =A1*Sheet2!A2 =A1*[A2]![Sheet2] =C3+A2!Sheet3 =A1*Sheet2:A2

=A1*Sheet2!A2

Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were longer than the average length of all songs on the list? =COUNTIF(D2:D23,"<"&AVERAGE(D2:D23)) =COUNTIF(D2:D23,">"&AVG(D2:D23)) =COUNTIF(D2:D23,">"&AVERAGE(D2:D23)) =COUNTIF(D2:D23,">"AVERAGE(D2:D23))

=COUNTIF(D2:D23,">"&AVERAGE(D2:D23))

Examine the image in the question. In "C3," we have the supplier address "Tucson, AZ 85721" appearing in a single cell (the formula combines data from cells "E3," "F3," "G3"). Which of the following formulas will achieve this result? =E3 + ", " +F3 +G3 =E3 &", " &F3 &" " &G3 =City, State/Province, Zip/Postal Code =SUM(E3:G3)+COUNT(C3)

=E3 &", " &F3 &" " &G3

If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use? =CASE(A1) =PROPER(A1) =PROPERCASE(A1) =LOWER(A1)

=PROPER(A1)

Examine the attached file: PS1_formulas.xlsx Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2? Cell C8 is the wrong cell to calculate the Quarter 2 sales total =SUM(B5:E5) =SUM(B4:B7) =SUM(C4:C7)

=SUM(C4:C7)

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total discounted cost of all the items purchased? =SUM(F2:F8) =TOTAL(F2:F8) =SUMIF(F2:F8) =AUTOSUM(F2:F8)

=SUM(F2:F8)

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box? !NULL# ?NULL? ~?NULL~? ~NULL~

?NULL?

Which of the following is NOT a reason to name a range of cells? A named range (e.g., 'Order_Total'), works across operating systems (e.g., works on both Macs & Windows), while using the cell references (e.g., H2:H100) does not work on different operating systems Naming a range of cells (e.g., 'Order_Total', for a range J2:J100) allows us to conveniently re-use the referenced cell range in multiple formulas A named cell, e.g., 'Sales_Revenue' can be used in formulas that require an absolute reference (and will allow copy-pasting without error) The name of a cell (or a range of cells), is often easier to remember than the cell reference itself (e.g., H57)

A named range (e.g., 'Order_Total'), works across operating systems (e.g., works on both Macs & Windows), while using the cell references (e.g., H2:H100) does not work on different operating systems

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.Which of the following methods should you use to explain your request to your assistant? Add a comment to the sales figure cell, and explain to your assistant what you want him to do Delete the sales figure for Monday (seeing the the blank cell will make things clear to the assistant) Set a fill color of dark red for the sales figure cell Make a pie-chart to explain the ratio of sales of Monday to other days in the week so the assistant sees which day has the

Add a comment to the sales figure cell, and explain to your assistant what you want him to do

Examine the attached file: PS1_formulas.xlsx In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively? Format Cells Quick Access toolbar Autofill =SUM() formula

Autofill

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? Ctrl + N Ctrl + E Ctrl + W Ctrl + B

Ctrl + N

Which of the following options scrolls the screen so that the active cell is visible? Ctrl+Backspace Ctrl+* Ctrl+[ Ctrl+]

Ctrl+Backspace

Examine the attached Excel file PS1_formatting.xlsx In Row 2 (Col B), which of the following formatting types has been applied to "3419.25041" to make it appear as "$3,419.25" ? formatting.xlsx General Number Currency Special

Currency

Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default? Developer Review Home Insert

Developer

Examine the screen-capture from Excel. What is the name of the open workbook? Microsoft Excel File Example_111.xlsx Calibri

Example_111.xlsx

What is the result of the formula=2^3 * (,10*10 Excel tells you that the formula contains an error 800 600 150

Excel tells you that the formula contains an error

Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this? F3 F6 F8 F9

F9

Which of the following command sequences will allow you to save a copy of your workbook with a different name? Ctrl, A Ribbon, Save File, Save As Data, Create Copy

File, Save As

The Analysis Toolpak can be activated through which sequence of steps? File-->Options-->Add ins -->Manage Excel Add-Ins --> Go Data-->Data Validation-->Manage Excel Add-Ins --> OK Tools-->Options-->Data-->What-if Analysis Home-->Ribbon-->Analyze-->Analysis Toolpak

File-->Options-->Add ins -->Manage Excel Add-Ins --> Go

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram? Filename (to import Data from Access) Ignore duplicates vs. Permit duplicates Simple vs. Compound Interest Input Range and Bin Range

Input Range and Bin Range

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 Insert --> Charts --> Bar --> Clustered Column Insert --> Charts --> Clustered Column Insert --> Charts --> Other Charts

Insert --> Charts --> Column --> Clustered Column

Which keyboard shortcuts can be used to move the active cell one column to the left? Left arrow or Shift + Tab Left arrow or Tab Left arrow or Ctrl + Tab Left arrow or Alt + Tab

Left arrow or Shift + Tab

Please refer to the figure above. What type of chart is shown in the figure? Line chart Bar chart Column chart Radar chart

Line chart

Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell? No No, it only deletes the cell's style Yes, but only if the data is text Yes

No

Which of the following statements is true? A workbook can have at most 3 worksheets. A worksheet can have many workbooks. A workbook can have many worksheets. A worksheet can have at most 3 workbooks.

A workbook can have many worksheets.

If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key) Absolute Relative Frozen Automated

Absolute

Would you be able to apply the "watch window" option in multiple sheets at the same time ? This option was available in Excel 2013 but not in Excel 2016 No Yes Yes, but only if there are no more than 3 sheets in the workbook

Yes

Please refer to the attached images. Go to the "View" tab on your Excel ribbon. Which command changes your view from what's seen in "worksheet_image1.png" to "worksheet_image2.png"? Zoom to Selection View Side by Side Freeze Panes None of the options described will work

Zoom to Selection

What characters are NOT allowed in sheet names ? [] , ! All options are correct

[]

You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry? a negative number a positive number a time a date

a negative number

Examine the 6th argument in the formula below. What kind of argument is it?=SUM(B1, 5, 6, 7, SQRT(4), A1:A5, TRUE) a range reference an empty argument a literal value a logical value

a range reference

Which of the following function(s) can be used to count error values in a range? all of the provided choices are valid ISERROR() ISERR() ISNA()

all of the provided choices are valid

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ . function signature dependent variables cell references arguments

arguments

Which of the following option is NOT available after right clicking on an Excel worksheet tab? changing the font color in the worksheet changing the color of the worksheet tab hiding the tab changing the name of the worksheet

changing the font color in the worksheet

Please refer to the figure. The address of the active cell consists of which of the following? column letter, row number column height, row width column letter row number

column letter, row number

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 a cell containing data in a numeric or date format this function is used primarily for blank or empty cells a cell with alphanumeric characters

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 current time current date and time nothing, it is not a valid Excel function

current date and time

What does the Excel keyboard shortcut Ctrl+Alt+F9 do? highlights error codes in the current workbook calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren't calculated. forces a complete recalculation of all formulas in all open workbooks. Ctrl+Alt+F9 will not do anything in Excel

forces a complete recalculation of all formulas in all open workbooks.

Examine the attached workbook. To create a duplicate copy of a chart (on same Excel worksheet, but different location), you can activate the chart and take what action? hold the "Alt" key while dragging the chart to the new location hold the "Shift" key while dragging the chart to the new location hold the "Ctrl" key while dragging the chart to the new location hold the "Tab" key while dragging the chart to the new location

hold the "Ctrl" key while dragging the chart to the new location

In Excel, what does the MODE function in a range return? most frequently occurring value in a range mean of the values in the range most frequently occurring text entry in a range middle value of the range

most frequently occurring value in a range

A chart in an Excel worksheet provides a visual representation of which of the following? numeric values formulae text values objects

numeric values

Fill in the blank:The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______. range (and) constant expression (and) criteria constant (and) expression range (and) criteria

range (and) criteria

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? to prevent blank cells in the copied range from overwriting existing data to skip pasting content into the blank cells to copy the blank cells from the copied range into the paste area None of the above

to prevent blank cells in the copied range from overwriting existing data

What does the following formula do? =COUNTIF(Data, TODAY()) Returns the number of cells containing the current date in the range named Data. Returns the number of cells that were last changed on the current date in the range named Data. "Returns the number of cells containing the text ""TODAY()"" in the range named Data." Returns the value of the cells containing the current date in the range named Data.

Returns the number of cells containing the current date in the range named Data.

Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1.Which command can you use to navigate to the comment in cell B5? Review (tab) -> Comments (command group) -> Next Review (tab) -> Comments (command group) -> Previous Home (tab) -> Navigate (command group) -> Next Comment Page Layout (tab) -> Search (command group) -> Jump To Comment

Review (tab) -> Comments (command group) -> Next

Please refer to the figure. Which of the following is the title of the chart displayed? Sales per month Sales (in thousands) Month Series 1

Sales per month

Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ? Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose Select cells A1 through D3 in Sheet1 -> Copy -> Transpose -> Go to Sheet2 -> Paste Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Formulas It is only possible if done manually

Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

Suppose you have a named range called "myrange" on Sheet1 of your workbook. If you delete the rows or columns that contain the range "myrange", the name (in Name Manager) contains an invalid reference. If you open name manager and examine what "myrange" refers to, you will see the missing cells denoted by ______. #VALUE! Sheet1!#REF! #DELETEDROWS Sheet1!#INVALIDREF!

Sheet1!#REF!

Which of the following is a shortcut key used to calculate only the formulas in the active worksheet? Shift+F9 all of the choices provided are valid F9 Ctrl+Alt+ F9

Shift+F9

While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called ? None of the (other) options provided is correct Side View Toggle Synchronous Scrolling

Synchronous Scrolling

If the formula =ROWS(Asset) * COLUMNS(Asset) that is used to calculate the number of cells in a range named Asset doesn't work, what does it imply? The range Asset consists of non-contiguous cells The formula syntax is incorrect, e.g., it should read =COLUMNS(Asset) * ROWS(Asset) The range Asset is a rectangular range of contiguous cells Some cells in the range are empty

The range Asset consists of non-contiguous cells

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 Formulas group (on the) Functions Functions group (on the) Insert Calculations group (on the) Formulas

Function Library group (on the) Formulas

After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to? Home Review Insert Formulas

Home