MIS 112 Problem Sets, Midterm

What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)? 10%, 400% 10%, 500% 10%, 300% 25%, 200%

10%, 400%

Within the "Number" tab, how many categories are available for displaying numbers? 12 8 10 14

12

With a single IF() function, how many actions can you ask Excel to carry out? 1 (i.e., an action if the criteria is true) 2 (i.e., an action if the criteria is true, or an an action if the criteria is false) 3 (i.e., an action if the criteria is any one of true, false, unknown) As many as you want, this is why IF() functions are so difficult to understand

2 (i.e., an action if the criteria is true, or an an action if the criteria is false)

How many sort options/commands do we have in Excel under the 'Data' tab? 1 2 3 4

3

The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly ______ characters of text 3 5 4 2

3

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

4500

Examine the attached worksheet. Which of the following formulas, when entered in cell E6, will give the Discount percentage for Pick Axe? =(1-(F6/D6))*100 =(D6/F6)*100 =(1-(D6/F6))*100 =(F6/D6)*100

=(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) Incorrect Response C20/F8 =SUM(C15:C19) =C20/F8 =PERCENTSALES(F22)

=C20/F8

Which formula can be used to find: How many songs were sung by Madonna and were three to four minutes long (inclusive)? =COUNTIFS(B2:B23,"Madonna",D2:D23,"<4",D2:D23,">3") =COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3") =COUNTIFS(B2:B23,'Madonna',D2:D23,'<=4',D2:D23,'>=3') =COUNTIFS(B2:B23,"Madonna",D2:D23,<=5,D2:D23,>=3)

=COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3")

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)

Which formula can be used to find: How many songs were sung by either Eminem or Spears? =SUM(COUNTIF(B2:B23,{"Eminem","Spears"})) =COUNTIF(B2:B23,{"Eminem","Spears"}) =IF(SUMIF(B2:B23,{"Eminem"}, D2:D23),SUMIF(B2:B23,{"Spears"},D2:D23)) =COUNTIFS(B2:B23,{"Eminem","Spears"})

=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))

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

Which of the following is TRUE with respect to charts? After you create a chart, you have great deal of flexibility in customizing it. Data is NOT needed to create a chart. Plot area contains all the elements of the chart. A Pie chart is another name for a Column chart.

After you create a chart, you have great deal of flexibility in customizing it.

Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" MIN() MAX() COUNT() SUM()

COUNT()

Which of the following is the default font type and font size in Excel 2016? Calibri, 11 point Arial, 12 point Times New Roman, 10 point Cambria, 14 point

Calibri, 11 point

Which of the following shortcuts allows you to enter the same data into multiple selected cells?Example: In a new Excel worksheet select cells A1:B4 and type "MIS111". The text is initially entered into cell A1. What could you do to replicate the same text "MIS111" in all the selected cells? Ctrl+Enter Ctrl+Shift Ctrl+Backspace Ctrl+Insert

Ctrl+Enter

Which of the following keyboard shortcuts can be used in Excel 2016 to create a table? Ctrl+F2 Ctrl+L Alt+T Ctrl+Alt+L

Ctrl+L

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" ? General Number Currency Special

Currency

What is metadata? Descriptive information about a workbook Corrupted data in a workbook The file extension of the workbook Comments and cell annotations in a workbook

Descriptive information about a workbook

Which of the following Excel tabs is NOT visible in a worksheet by default? Developer Review Home Insert

Developer

When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell? Currency formatting is not applied to the new cell. Excel applies currency formatting to the new value in that column cell. A special type of formatting other than currency formatting is applied to the new value. Currency formatting is temporarily applied to the new value (but only persists until the Workbook is closed).

Excel applies currency formatting to the new value in that column cell.

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 The keyboard shortcut to add a new worksheet is: Shift + F11 You can insert a new worksheet by clicking on the "Insert Worksheet" control to the right of the last worksheet tab Right clicking a worksheet tab, followed by choosing the "Insert" option, allows you to add a new worksheet to Excel

Excel does not allow you to add additional worksheets to a workbook that has been saved previously

Excel uses column headers to create labels for each _____ on data entry form. Record Data Form Row Field

Field

How do you find the width of a column?

Format, cell size, column width

Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 ) This type of IF() functions with an embedded OR() is no longer supported in Excel 2013. Such IF() statements are too complicated for use in business. They are only used by NASA. In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE Using a nested IF() in a formula is not recommended because the formula result is different based on the IQ level of the user.

In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE

When a column is sorted within a table, which of the following is true about the drop-down list in the header row? The header row background fill color changes to a darker shade It displays a different graphic to remind you that the table is sorted by that column There is no change to the header for that column before or after sorting The header row (for that column) is now disabled and until the sort is cleared, no further actions are permitted

It displays a different graphic to remind you that the table is sorted by that column

Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on). Shapes Categories Layouts Attributes

Layouts

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

What is the name of the company that created Excel? Microsoft Google Apple Dell

Microsoft

Please examine the attached image. A part of the workbook is boxed in red. (under the ribbon, left corner) Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______ Formula bar. Any formula in the current cell is displayed here. Cell box. The address of a range of cells is displayed here. Column box. The address of the topmost cell in a range is displayed here. Name box. The number of rows and columns in the selection is displayed here.

Name box. The number of rows and columns in the selection is displayed here.

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

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" must appear in the cells surrounded by asterisks (i.e., the * character). The word "budget" can appear anywhere within the cell. It looks specifically for cells containing budget multiplied by some number. For example, a cell containing the formula: "=budget*0.25". Only cells with the text "budget" at the beginning or the end of the cell, are counted. Thus a cell with "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted.

The word "budget" can appear anywhere within the cell.

What of the following is FALSE about filtering a table? You can filter by multiple values in a column by using multiple check marks. You can filter a table using any number of columns. You can choose "Text Filters" or "Number Filters" to display only the rows you are interested in. When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows Wide Narrow Transformed Empty

Wide

If you copy the whole cell "B8" (not only its formula) and paste it into cell "D8" (shaded in green), will it produce the correct total sales value for Quarter 3? Yes No, because you will end up with the total for Quarter 1 in that cell No, because you must re-enter the formula for each quarter Answer depends on whether Excel 2007 or Excel 2010 is used

Yes

Is it possible to convert a cell with text data into a .png format ? No This option was available in Excel 2013 but not in Excel 2016 Yes Yes, but only if the cell width is no larger than 72 pixels

Yes

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

Yes

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 Incorrect, as adding rows is not supported False, Data forms work only with Tables, not with Ranges No, Data forms work only with Ranges, not with Tables

Yes, all these are valid form operations

Which of the following does the Formula Autocomplete feature in Excel take into account? all of the options provided for this answer are valid Excel built-in functions user-defined functions defined names

all of the options provided for this answer are valid

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

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

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

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

What is the red box at the top of an excel spreadsheet?

ribbon

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______. select a row select a column select a range select multiple rows or columns

select a column

What Excel element is shown in the red box? (has the excel symbol, save, undo, etc.) File button Quick Access toolbar Scrollbar Ribbon

Quick Acces toolbar

Adding comments to a cell is available through which tab of the Excel ribbon? Home Formulas Data Review

Review

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________. Ctrl+Alt Shift+F2 F5 Ctrl+A

Shift+F2

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 ? Toggle None of the (other) options provided is correct Synchronous Scrolling Side View

Synchronous Scrolling

Excel formatting tools are available at the following locations: The Format Cells dialog box and the Home Tab of the Ribbon On the Home Tab of the Ribbon only On the Mini toolbar that appears when you right-click a range or a cell, and the Home Tab of the Excel Ribbon The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar

The Format Cells dialog box, the Home Tab of the Ribbon, the Mini toolbar

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