MIS 112 SAR 1 Study Guide

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?

0.00388865177938274

In the attached spreadsheet, please use an appropriate formula to compute the total of the Cells C2 through C11 (surrounded by a red border in the worksheet). What is this total?

14787.45

Please refer to the figure. How many worksheets are contained in the workbook shown?

5

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

In the attached Excel workbook PS1_Comments_Calculation.xlsx , which of the following formulas can be used to calculate Sophie's total score in both the exams?

=B4+C4

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)

=C20/F8

Which formula can be used to find: How many songs were sung by singers whose names begin with S?

=COUNTIF(B2:B23,"S*")

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

Please see the attached workbook. The result of an HLOOKUP function is "yellow." What is the formula used to obtain this result?

=HLOOKUP("Color",A1:B5,3,FALSE)

Please see the attached workbook. If the 'amount' for the given 'date' is to be retrieved from the given range of data (I4:K7), which of the following formulas should be used?

=INDEX(I4:K7,MATCH(B2,I4:I7,0),3)

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)

Please see the attached workbook. What formula (in cell B3) returns the tax rate for the income in cell B2?

=VLOOKUP(B2, D2:F7, 3)

What will be returned if an Excel LOOKUP() based formula has an entry for the lookup_value and the lookup_vector, but no entry for a result_vector parameter?

A value from the lookup_vector

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

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

What is the keyboard shortcut to clear the formatting in a cell ?

Alt -> h -> e -> f

Examine the attached workbook. Fill in the blank: You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add Chart Element --> __________

Axis Titles

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: Column, Chart 2: Bar

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

Ctrl + N

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

D7

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

Developer

When you select a cell in the Total Row in a table, a drop-down arrow appears in the cell. Click the arrow and you can select the "Count" formula. What does this formula do?

Displays the number of entries in the column.

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.

Please use the attached spreadsheet to test your answer (it has a range "salary" defined).If your formula uses named cells or ranges, you can either type the name in place of the address, or choose the name from a list and have Excel insert the name for you automatically. You can also press the ____ button which displays the Paste Name Dialog box. If no names are defined, pressing this button has no effect.

F3

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

F4

The Analysis Toolpak can be activated through which sequence of steps?

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

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 )

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

Which of the following is the MOST compelling motivation for using HLOOKUP()?

It usually reduces duplicate data storage

Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell)

Left Align

In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting.

Manage rules

Can you print data in an Excel Form attached to a table?

No, you cannot

Please see the attached Excel workbook image. The cells highlighted in yellow contain a Total Row. How can such a Total Row be added to a table?

Once you have a table, you can click the "Table Tools / Design" tab. Then, under "Table Style Options" check the box for "Total Row."

In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50)

Products!F2:F50

What is the primary purpose of "Protected View?"

Protect you from malware

Adding comments to a cell is available through which tab of the Excel ribbon?

Review

Please see the attached workbook. Which of the following steps allow you to add a comment to a specific cell? (When you move over the cell with your mouse, the comment will pop up)

Review -> Comments -> New Comment

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

Ribbon

Please see the attached workbook. Cell C7 currently has a number entered manually. This number should be replaced with a formula. What is the best formula to use in cell C7?

SUM(B$2:B7)

Please see the attached workbook image. What sequence of steps will allow you to format the data into a table?

Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon

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

Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook?

Shift + S

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

Please examine the attached workbook. Which company has the highest income? ( Income = Sales - Expenses )

Tesco

Excel formatting tools are available at the following locations:

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

Please see the attached workbook, Sheet1. What happens when you apply a theme on Sheet1 of the Excel sheet by going to Page Layout -> Themes

The theme is applied to all the sheets in the workbook

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" can appear anywhere within the cell.

Which rule category does the following conditional formatting rule belong to? "Highlight the top 10 items"

Top/Bottom rules

Which of the following is an advantage of using Excel tables?

Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas

Please examine the attached workbook. What type of chart is included in it?

Waterfall

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

In an Excel Form, when finding a row by entering search criteria: Will the question mark symbol "?" find or match any single character?

Yes, the "?" symbol is used to find/match a single character

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

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

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

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

column

Please refer to the figure. The address of the active cell consists of which of the following?

column letter, row number

Which of the following does the function =NOW() return?

current date and time

Which of the following is NOT a state an Excel workbook can be in?

expanded

For a range called Data, what does the function =COUNTIF(Data, "<>0") return?

number of cells not equal to 0

You select a cell with data "120" in an Excel worksheet and press the "Delete" key on your keyboard. Which of the following holds TRUE?

only the contents of the cell are removed

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?

passwords are case sensitive

Which of the following options best describe what the following formula would do? =VLOOKUP(D2,PAGES!A2:B10,2,TRUE)

perform a vlookup in a different worksheet which does not have an exact match

Using VLOOKUP in some ways is similar to using a(n) _______ .

phonebook

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?

#N/A

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

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

12

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

Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?

?,*

Which of the following statements is true?

A workbook can have many worksheets.

How can you edit the contents of cell (by entering cell-edit mode)? Choose the best answer.

All of the options provided are correct

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

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

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

Bottom

Which of the following is a VALID sequence of options for selecting a different chart layout?

Design (from Chart Tools) --> Chart Layouts

Excel uses column headers to create labels for each _____ on data entry form.

Field

Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell?

No

What characters are NOT allowed in sheet names ?

[]

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

absolute

What does the Excel keyboard shortcut Ctrl+Alt+F9 do?

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

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______.

select a column