MIS 112 Problem Sets #5-#6

Examine the attached worksheet. Using the ANOVA: Single Factor Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the correct value for the p-value of the data in the worksheet? DA_Test.xlsx A) 0.00388865177938274 B) 3.8333333333333 C) 2.5 D) 0.12

0.00388865177938274

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? A) 45000 B) 500000 C) 4750 D) 6750

4500

Advanced_aggregation.xlsx Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters? A) =COUNTIF(B2:B23,"6*?") B) =COUNTIF(B2:B23,"{6?}") C) =COUNTIF(B2:B23,"??") D) =COUNTIF(B2:B23,"??????")

=COUNTIF(B2:B23,"??????")

Advanced_aggregation.xlsx 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? A) =COUNTIF(D2:D23,"<"&AVERAGE(D2:D23)) B) =COUNTIF(D2:D23,">"&AVG(D2:D23)) C) =COUNTIF(D2:D23,">"&AVERAGE(D2:D23)) D) =COUNTIF(D2:D23,">"AVERAGE(D2:D23))

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

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 assitant? A) Add a comment to the sales figure cell, and explain to your assistant what you want him to do B) Delete the sales figure for Monday (seeing the blank cell will make things clear to the assistant) C) Set a fill color of dark red for the sales figure cell D) 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

What option in the Table Tools / Design tab removes a table without losing the data in it? A) Remove Duplicates B) Summarize with Pivot Table C) Convert to Range D) Delete Table

Convert to Range

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

Field

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to ass, edit, find, and delete rows. A) Wide B) Narrow C) Transformed D) Empty

Wide

cells_example.xlsx Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote? A) a comment exists for that cell B) the cell has an error C) wrong value / formula in the cell D) the cell references a missing value

a comment exists for that cell

The ____ command is sued to display text on multiple lines in a cell. A) shrink to fit B) wrap text C) merge cells D) icon formatting

wrap text

Which of the following is TRUE about sorting a table? A) Sorting a table is not possible. B) Words cannot be sorted, while numbers can be sorted. C) Sorting a table re-arranges the rows based on the contents of a particular column. D) Sorting the rows based on the background or text color of the data is not possible.

Sorting a table re-arranges the rows based on the contents of a particular column.

Examine the attached worksheet. using the Covariance Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the covariance coefficient for Control and Relaxation Therapy? DA_Test.xlsx A) 1 B) 2 C) 4 D) 3.2

1

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

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

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

3

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

?NULL?

Which of the following functions can be used to count specific types of errors? A) COUNTA B) COUNTIF C) COUNTBLANK D) COUNT

COUNTIF

Which of the following is the default font type and font size in excel 2016? A) Calibri, 11 point B) Arial, 12 point C) Times New Roman, 10 point D) Cambria, 14 point

Calibri, 11 point

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

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

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

In this formula, the second statement in the OR (i.e., checking if 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? A) The header row background fill color changes to a darker shade B) It displays a different graphic to remind you that the table is sorted by that column C) There is no change to the header for that column before or after sorting D) The header row 9 (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

tables_duplicate_rows.xlsx For this question, please download the attachedExcel workbook. Convert the data on Sheet1 into a table. Next answer the following question: If you proceed to remove duplicate values using the "Remove Duplicates" feature in "Table Tools", will Excel remove one of the two table rows? A) Yes, because Excel can see that the dates in the two cells are the same value. B) No, because dates that are a duplicate cannot be removed. C) No, because excel does not let you remove duplicates when you only have 2 rows in a table (removing a row makes it a 1 -row, which is not really a table). D) Yes, because the duplicate values are determined by the value displayed in the cell and not necessarily by the value stored in the cell. E) No, these are not considered duplicates by excel. To remove the duplicate data you muse ensure that the date the date in the second row is formatted the same way as the date in the first row.

No, these are not considered duplicates by excel. To remove the duplicate data you muse ensure that the date the date in the second row is formatted the same way as the date in the first row.

Can you print data in an Excel Form attached to a table? A) No, you cannot B) Yes, you can always perform an action like Printing in Excel C) You can print forms with the "Print" button on the Form only D) Printing is disabled unless you buy the "Print Form" excel Professional feature app from Microsoft

No, you cannot

Reading_Symbol Reference.xlsx Please refer to the attached excel workbook. How can you add a dollar ($) symbol to the values in a column as seen in the "Grand Total" column? A) The simplest way is to click on the "Wrap text" button in Home tab. From the dialog box that pops up, pick the default $-symbol formatting. B) This is automatically provided to you when you make an Excel Table. C) One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" with a $ symbol and 2 decimal places) as your formatting option from within the Number tab D) the correct way to do this is by going through the "Format table" menu, and selecting the option of "Number" (with 2 decimal places and a $ symbol)

One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" with a $ symbol and 2 decimal places) as your formatting option from within the Number tab

What does the following formula do? =COUNTIF(Data, TODAY())) A) Returns the number of cells containing the current date in the range named Data. B) Returns the number of cells that were last changed on the current date in the range named Data. C) "Returns the number of cells containing the text "TODAY()"" in the range named Data" D) 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? A) Review (tab) -> Comments (command group) -> Next B) Review (tab) -> Comments (command group) -> Previous C) Home (tab) -> Navigate (command group) -> Next Comment D) Page Layout (tab) -> Search (command group) -> Jump To Comment

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

What does selecting the option "Current region" within the; Go To Special Dialog Box (Home (tab) -> editing (command group) -> Find & Select) do? A) Selects the entire array B) Selects all graphic objects on the worksheet C) Selects a rectangular range of cells around the active cell D) selects all empty cells

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 and press the following keyboard shortcut: __________. A) Ctrl+Alt B) Shift+F2 C) F5 D) Ctrl+A

Shift+F2

The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the words "budget" in certain positions within the text. Which of the following best describes the cells counted? A) The word "budget" must appear in the cells surrounded by asterisks (i.e., the * character). B) The word "budget" can appear anywhere within the cell. C) It looks specifically for cells containing budget multiplies by some number. For example, a cell containing the formula: "=budget*0.25". D) Only cells with the text "budget" at the beginning or the end of the cell, are counted. Thus a cell "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted.

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

Histogram.xlsx 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 you would see? A) The histograms would be identical B) Three of the Histogram bins will have a different count C) The histograms would be identical except for the Horizontal axis labels ( taken from the "Ranges" column) D) Two of the Histogram bins will have a different count

Three of the Histogram bins will have a different count

Which of the following is NOT a method of creating frequency distributions? A) use the Review (Proofing) tool of Spelling Error Frequency per Worksheet B) Write your own formula or function C) Use the Pivot table feature D) Use the Analysis ToolPak add-in

use the Review (Proofing) tool of Spelling Error Frequency per Worksheet