MIS 112 Midterm Problem Sets

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) ":" (colon) ";" (semi colon) " " (space)

"," (comma)

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

In the attached image, examine the formula in cell "E9." What result will this formula produce? 20 882 2 #VALUE!

#VALUE!

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 highest sale

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

Which of the following is TRUE of the "Protect" option in an Excel Workbook? it helps prevent unauthorized users from copying existing worksheets it helps prevent unauthorized users from revealing hidden sheets it helps prevent unauthorized users from inserting new worksheets All of the above options are true

All of the above options are true

The =SUM() function does which of the following in Excel? Allows you to total up numbers in a specified range of cells Tells you the cumulative sum of interest paid between two time periods Shows you the current date and time Nothing, it is not a valid Excel function

Allows you to total up numbers in a specified range of cells

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 keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards? Ctrl + X Ctrl + V Ctrl + C Ctrl + Z

Ctrl + X

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

Which of the following is a VALID method of hiding rows or columns? Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list) Click on the Row/Column header, (then) right-click and choose Delete Right-click on the cell, (then) choose "Insert" Home (ribbon tab), (then) Paste (in Clipboard group)

Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)

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

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

Synchronous Scrolling

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? No, because the result will be the total for Quarter 1. No, because you must directly enter the formula for each quarter in cell C6. Yes for Excel 2013, but not for previous versions of Excel. Yes, the formula range will be adjusted automatically

Yes, the formula range will be adjusted automatically

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 [ ] ! ,

[ ]

What type of cell reference does the formula in E2 use? (refer to the attached image, the formula is highlighted) price oriented incorrect absolute relative

absolute

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

When a range of cells is selected, which of the following occurs? the selected cells are all highlighted in the same color none of the cells is highlighted in any color a box marks the boundary of the selected cells, but none of the cells are highlighted all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

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

Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do? pastes values and number formatting paste values in color pastes values with a percentage formatting pastes contents with values and formatting

pastes values and number formatting

Examine the attached worksheet. Using the Correlation Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the correlation coefficient for Relaxation Therapy and Music Therapy? DA_Test.xlsx 0.56 0.05 0.39 1

0.56

Please refer to the figure. 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 action if the criteria is false)

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

Examine the attached Excel file. When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas? =$B$7*$F$2 =B$7*$F2 =B7*F2 =$B7*F$2

=$B7*F$2

Examine the attached worksheet. Which of the following formulas, when entered in cell B10, will give the average quantity of all the items purchased? Formulas_Test.xlsx =AVERAGE(B2:B8) =AVG(B2:B8) =AVERAGE(C2:C8) =AVG(C2:C8)

=AVERAGE(B2:B8)

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 formulas will display both the current date and time when entered into a cell in an Excel worksheet? = NOW() = DATE() = TIME() = TODAY()

=NOW( )

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"}))

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? Formulas_Test.xlsx =SUM(F2:F8) =TOTAL(F2:F8) =SUMIF(F2:F8) =AUTOSUM(F2:F8)

=SUMIF(F2:F8)

Examine the image in the question. Which of the following is true for the new name being defined? A name is being applied to a range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook. A new name is being created for use specifically in the current worksheet (Sheet1), based on the given range (B4:E4). The UA range is being moved from cells B5:E5 to cells B4:E4. Three ranges, "UA", "ASU" and "NAU" are being defined as new names in the workbook.

A name is being applied to a range B4:E4. This name "UA", can be used in formulas anywhere in the current workbook.

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 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

Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z? AA AZ ZA ZZ

AA

In the image shown below, identify the correct chart types. You can use the data in the attached Excel workbook (charts_SampleData.xlsx) to try different charts. Chart 1: Bar, Chart 2: Column Chart 1: Column, Chart 2: Bar Chart 1: Line, Chart 2: Bar Chart 1: Column, Chart 2: Line

Chart 1: Column (Horizontal); Chart 2: Bar (Vertical)

Examine the attached workbook. To move "Chart 1" in "Sheet1" to a new Excel worksheet named "MyChart", which options will you use? Click on: (Chart Tools) Design tab --> Move chart (Location) --> Object in (option) Click on: (Chart Tools) Design tab --> Move chart (Location) --> New Sheet (option) Create a new sheet and drag the chart None of the other options will work

Click on: (Chart Tools) Design tab --> Move chart (Location) --> New Sheet (option)

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 Copies the value in cell C12 to cell A1 Copies the value in cell A1 to cell C12 Subtracts the value in the cell C12 from the value in the cell A1

Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE

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 attached workbooks. The worksheet in "worksheet_file1.xlsx" is split into 4 panes (shown in "worksheet_file2.xlsx"). What cell must be selected prior to applying the split function in "worksheet_file1.xlsx" so that it looks exactly like what you see "worksheet_file2.xlsx"? C6 C7 D7 D6

D7

Please examine the attached workbook. Which conditional formatting rule is applied? PS2_Chart3 Custom bars Icon sets Color scales Data bars

Data bars

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

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

Developer

Please refer to the figure above. What are the small bars highlighted by the orange ovals known as? Error bars Gridlines Trendlines Up-Down bars

Error bars

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

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

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. Incorrect Response 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

Examine the survey results in the attached workbook (in Sheet2). What type of chart would be most suitable to represent the survey response data? Pie Scatter High-Low-Close Area (2D or 3D)

Pie

What happens if you try to store a number greater than 15 digits in Excel ? Replace the digits exceeding 15 with the number zero Stores as is Replaces the digits exceeding 15 with '#' None of the (other) options provided is correct

Replace digits exceeding 15 with the number zero

Please refer to the figure. What Excel element is shown in the red box?

Ribbon

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

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

Which of the following methods is a VALID way to get into cell edit mode? Clicking the cell one time enables you to edit the cell contents Selecting the cell that you want to edit, and then clicking in the Formula bar Pressing F9, which enables you to edit the cell contents directly in the cell Pressing F4 allows you to the edit the cell contents

Selecting the cell that you want to edit, and then clicking in the Formula bar

Examine the attached Excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram (you can use the Analysis Toolpak for that), and compared it with the histogram in the image, which of the following options best describes what would you see? The histograms would be identical Three of the Histogram bins will have a different count The histograms would be identical except for the Horizontal axis labels (taken from the "Ranges" column) Two of the Histogram bins will have a different count

Three of the Histogram bins would have a different count

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

Yes

Charts are dynamic, e.g., they change as the underlying data changes. Is this an accurate statement? No, they are static by default, and you must press "Ctrl-F7" to update them. Yes, by default they are dynamic. No, as this quality would lead to charts with circular references. Yes, but additional options must be specified to make charts dynamic.

Yes, by default they are dynamic.

Which of the following characters is VALID for use in an Excel workbook file name? _ : " \

_

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 the cell has an error wrong value / formula in the cell the cell references a missing value

a comment exists for that cell

Which of the following best describes what a Sparkline is? a mini-chart that is displayed in a single cell a blinking line to separate two cells a highlighted cell a dynamic line chart where the lines have metallic color effects

a mini-chart that is displayed in a single cell

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

A formula in Excel can consist of which of the following? all of the choices provided are valid text cell references mathematical operators

all of the choices provided 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

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

A group of cells is referred to as which of the following? group range pack class

range

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

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