MIS 112 Final

Examine the attached file: PS1_formulas.xlsx Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the year?

$24,914

What is the file format (file extension) used when you save an Excel workbook as a single file webpage?

.mht or .mhtml

Which of the following is the file format of the Excel 97-2003 workbook?

.xls

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

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

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 will be the result of this formula =IF(A1<100000,A15%,A17.5%), if the cell A1 has a value of 90000? A) 45000 B) 500000 C) 4750 D) 6750

4500

Advanced_aggregation.xlsxUse 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.xlsxUse 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))

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total number of items for which there was no discount offered (i.e., discount is 0)?

=COUNTIF(E2:E8, 0)

Please see the attached workbook. The grade of the student should be "Pass" if his/her score is greater than 65. Otherwise, the grade should be "Fail". Which of the following formulas could you use for this scenario?

=IF(D2>65,"Pass","Fail")

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 statements is true?

A workbook can have many worksheets

Which of the following file formats can be opened using Excel 2016? A) XLS B) XLT C) XLA

All the other options are correct

Which of the following types of Cell Formating can be achieved using the Format Cells feature of Excel? All the other options are correct A) Alignment B) Fill C) Protection

All the other options are correct

The =SUM() function does which of the following in Excel?

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

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?

Autofill

Which of the following is an INVALID horizontal text alignment option provided in Excel?

Bottom

What option in the Table Tools / Design tab removes a table without losing the data in it?

Convert to Range

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?

Either of the previous options (resetting through the menu or the right-click options work)

Examine the screen-capture from Excel. What is the name of the open workbook?

Example_111.xlsx

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?

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

Which older version of Excel file can Excel 2016 NO LONGER open?

Excel can open all of these files.

While using cell references in Excel, what key is used to change the type of the reference?

F4

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

Field

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

Light, Medium and Dark are the categories of styles found under the ______ command [within the Home (tab) -> Styles (command group)].

Format as Table

Please use the attached workbook as an external data source. 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 (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value lesser than 30000 for 'Salary' column

From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list

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

Examine the attached workbook. Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"?

It prints only the chart on that sheet

Which keyboard shortcuts can be used to move the active cell one colunn to the left?

Left arrow or Shift + Tab

The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?

Link_location

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

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

Quick Access toolbar

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.

Please refer to the figure. Which of the following View ribbon tab options was used to display the horizontal bar between the nonconsecutive rows containing "Astoria, OR" and "Victoria, TX"?

Split

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 ?

Synchronous Scrolling

Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.

Table

In Excel, what does the MODE function in a range return?

most frequently occurring value in a range

Data retrieved from Web Queries do not include __________ and contents of __________.

pictures, scripts

Step 3 of the Query Wizard lets you specify how you want the ______.

records to be sorted

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

What form of LOOKUP is used when you have a large list of values to look up or when the values may change over time?

vector form

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

The ____ command is used to display text on multiple lines in a cell.

wrap text

Please see the attached workbook. Which LOOKUP function would be used in order to get the Tax Rate (in cell B3) for the entered income (in B2)?

VLOOKUP

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

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

Which of the following does the Formula Autocomplete feature in Excel take into account?

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

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

arguments

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

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?

Ctrl + N

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

Developer

What is the correct order of steps to be followed when you use a query wizard?

1. choosing the columns, 2. filtering data, 3. sort order, and 4. finish

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

12

Please see the attached workbook. How many names does the workbook have defined?

2

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)

Please see the attached workbook. What value does the formula "=VLOOKUP("John",A2:D13,3,FALSE)" return?

2000

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

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

AA

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

Which of the following is TRUE with respect to charts?

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

Which of the following keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards?

Ctrl + X

What is the shortcut key to create hyperlink when you select an object or a cell?

Ctrl+K

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

Which of the following actions should be taken while creating a table to display Table Headers?

Check 'My table has headers'

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

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 is metadata?

Descriptive information about a workbook

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

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 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 you right-click a graphic object and choose Size and Properties from shortcut menu, Format Shape pane opens. If you expand the Properties section which of the following choices you get? A) Move and Size with cells B) Move But Don't Size with cells C) Don't Move or Size with Cells

Move and Size with cells Move But Don't Size with cells Don't Move or Size with Cells

tables_duplicate_rows.xlsxFor 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

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned?

None of the options provided is correct

Please refer to the attached Excel workbook. How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?

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

Reading_Symbol Reference.xlsxPlease 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

Which of the following is NOT one of the methods for 'Conditional Formatting' in Excel?

Pie Chart

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

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

Ribbon

Please see the attached workbook. Which of the following is the correct sequence of steps to be followed to see employees with a salary over 2000?

Select cells for salary, click on "Conditional Formatting" under the Styles command group, under "Highlight Cells Rules" choose "Greater than", then enter 2000

Which of the following methods is a VALID way to get into cell edit mode?

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

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

Examine the attached Excel file. In row 4, we have two different ways of displaying the August 25 date. Which of the following is TRUE about these dates?

The data stored in the two cells A4 and B4 are identical

Examine the attached file: PS1_formulas.xlsx 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

Would you be able to apply the "watch window" option in multiple sheets at the same time ?

Yes

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?

Yes, the formula range will be adjusted automatically

cells_example.xlsxPlease 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

Which of the following best describes what a Sparkline is?

a mini-chart that is displayed in a single cell

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

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

A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?

cell

Which of the following option is NOT available after right clicking on an Excel worksheet tab?

changing the font color in the worksheet

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?

column