MIS 112 Midterm 1

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

By default, what is the extension used when you save a workbook in Excel 2016?

.xlsx :)

What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)?

10%, 400%

Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers? Category: General (highlighted) :

12

With a single IF() function, how many actions can you ask Excel to carry out?

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:

3:)

Please refer to the figure. How many worksheets are contained in the workbook shown? Summary Data for Project Completion, projects completed on time and projects completed within budget within 4 quarters....:

5:) shows the summary sheet, green team, blue team, red team, yellow team:)

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

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

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)? Formulas_Test_v2.xlsx :

=COUNTIF(E2:E8, 0)

If cell "A1" contains JOHN F. SMITH and you want this value to be changed into a proper case like John F. Smith, what Excel function would you use?

=Proper(A1)

Which of the following best describes what a Sparkline is?

A mini-chart that is displayed in a single cell

charts_sampleData.xlsx 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:)

formulas_Inserting_Range_Names_in_Formulas.xlsx 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

Examine the screen-capture from Excel. Which cell is currently active in the figure?

F4

Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this?

F9

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

File, Save As:)

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

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

Layouts

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

To display the Total Row in a Table: choose Table Tools / Design -> Table Style Options (command group) and put a check mark next to Total Row. Selecting a cell in the Total Row and clicking on the drop-down arrow that appears in that cell will NOT allow you to select which of the following summary formulas?

Mode

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:

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

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ?

Shift + Tab:)

Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?

Single-factor without replication

Which of the following chart types does NOT support the Data Table chart element?

Treemap

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

all of the options provided for this answer are valid

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

most frequently occurring value in a range

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

Developer:) Study a lil more

Which of the following is NOT a type of cell reference?

Dynamic

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

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

Passwords are case sensitive

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

Wrap Text

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

AA:)

Charts_example.xlsx Examine the attached workbook. If you want to remove chart labels, where do you find the options to do so within the Design tab (Chart Tools)?

Add Chart Element > Data Labels

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

Which of the following options moves the active cell right one screen?

Alt+PgDn

What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open?

Ctrl + H:)

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

Ctrl + N:)

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

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

Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value?

ISOLOGICAL

Cells_image.png Please examine the attached image. A part of the workbook is boxed in red. Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______ :

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

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

No :)

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

No, you cannot:)

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

Tables_moving_tables.xlsx For this question, please download the attached Excel workbook. Convert the data on Sheet1 into a table. Next answer the following question: What keys do you need to press to select the newly created table so that it can be copied or moved to a different worksheet?

Press Ctrl+A

Please refer to the figure. What excel element is shown in the red box? (row above file, edit, insert, etc... it is where the save buttons and the undo and redo buttons are)?

Quick Access Toolbar:)

What does the following formula do? =COUNTIF(Data, TODAY()):

Returns the number of cells containing the current date in the range named Data.

While comparing 2 sheets using the "View Side By Side" feature, there is an option which lets you scroll the 2 sheets in sync. What is that option called?

Synchronous scrolling:)

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

What of the following is FALSE about filtering a table?

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

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

Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate?

Yes, the statement is accurate:)

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"? ADAMS AND CHUNG:

Zoom to Selection

What characters are NOT allowed in sheet names?

[]

Cells_example.xlsx 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:)

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