MIS 112 Final 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)

Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned? #REF! Excel returns a "No value was found" error message #N/A An approximate match is returned

#N/A

Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected. #REF! #NUM! #N/A #NAME?

#NUM!

Consider the phrase: "This is Excel" (strikethrough). Which sequence of keyboard shortcuts result in the formatting used on the words "This is Excel" in the earlier sentence (within Excel, on a Windows PC)? (Control + 5) + (Control + F4) (Control + B) + (Control + 5) (Control + I) + (Control + B) (Control + C) + (Control + V)

(Control + B) + (Control + 5)

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 2 4 3.2

1

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 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 5 4 2

3

What is the maximum number of characters a worksheet name can contain? 31 34 28 32

31

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 will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000? 6750 4750 4500 500000

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

Which of the following shows the correct syntax for referencing cell "A2" in another worksheet? =A1*Sheet2!A2 =A1*[A2]![Sheet2] =C3+A2!Sheet3 =A1*Sheet2:A2

=A1*Sheet2!A2

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 begins with the letter P and contains exactly four characters? =COUNTIF(A1, "P???")=1 =COUNTIF(A1, "P???")=4 =COUNTIF(A1, P???)=4 =COUNTIF(A1, P???)=1

=COUNTIF(A1, "P???")=1

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) =SUMIF(E2:E8, 0) =COUNTIF(E2:E8, Discount=0) =SUMIF(E2:E8, Discount=0)

=COUNTIF(E2:E8, 0)

Use the attached spreadsheet to answer the following question. 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")

Please see the attached workbook. The result of an HLOOKUP function is "yellow." What is the formula used to obtain this result?dataLOOKUP_Ex5.xlsx =HLOOKUP("FRUIT",A1:B5,3,FALSE) =HLOOKUP("Color", A1:B2,3,FALSE) =HLOOKUP("Color",A1:B5,3,TRUE) =HLOOKUP("Color",A1:B5,3,FALSE)

=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? dataLookup_eg7.xlsx =INDEX(I4:K7,MATCH(B2,I4:I7,0),3) =INDEX(I4:K7,3) =MATCH(B2,I4:I7,0) =MATCH(I4:I7,0)

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

Please see the attached workbook. If you have to use a formula to retrieve the age of the student from the given array of data, which of the following formulas will you use? dataLookup_eg6.xlsx =INDEX(J4:L8,3,2) =INDEX(J4:L8,2,3) =INDEX(J4:K8,1,3) =INDEX(J4:L8,3,3)

=INDEX(J4:L8,3,3)

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? =CASE(A1) =PROPER(A1) =PROPERCASE(A1) =LOWER(A1)

=PROPER(A1)

Examine the attached file: PS1_formulas.xlsx. Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2? Cell C8 is the wrong cell to calculate the Quarter 2 sales total =SUM(B5:E5) =SUM(B4:B7) =SUM(C4:C7)

=SUM(C4:C7)

Use the attached spreadsheet to answer the following question. 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"}))

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

?NULL?

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

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

Which of the following types of Cell Formating can be achieved using the Format Cells feature of Excel? All the other options are correct Alignment Fill 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 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 options moves the active cell right one screen? Alt+PgDn Alt+PgUp PgDn PgUp

Alt+PgDn

Which of the following is TRUE about icon sets? You can supply your own icons. Excel provides ten icon sets to choose from. An icon can be used to display a trend graphically. Arrow-style icons are not available for use, but bow-shaped icons are.

An icon can be used to display a trend graphically.

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

Suppose you have a range of cells with numbers and a few blank entries. If you sort the range in ascending order (smallest to largest), where will the blank cells be located after sorting? At the beginning At the end Excel cannot sort ranges with blank values Excel leaves the blank values in their original location

At the end

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? Format Cells Quick Access toolbar Autofill =SUM() formula

Autofill

Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" You may refer to the image in the question. MIN() MAX() COUNT() SUM()

COUNT()

Which of the following functions can be used to count specific types of errors? COUNTA COUNTIF COUNTBLANK COUNT

COUNTIF

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

Cell

Which of the following actions should be taken while creating a table to display Table Headers? Uncheck 'include names' Check 'My table has headers' Check 'Display headers' Uncheck 'Hide table headers'

Check 'My table has headers'

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

Which keyboard shortcut is used to select all cells in an open Excel worksheet? Ctrl + A Ctrl + X Ctrl + V Ctrl + C

Ctrl + A

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press _________ . Ctrl + Alt + Space +F9 Ctrl + Alt + Shift + F9 Ctrl + Alt + Fn + F9 Ctrl + Alt + Shift + F10

Ctrl + Alt + Shift + F9

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

When entering / editing an Array formula, press _________ to enter the formula (or Excel does not recognize the formula as an Array formula). Ctrl + Shift + Enter Alt + Ctrl + Delete Alt + Shift + Enter None of the options provided is correct

Ctrl + Shift + Enter

What is the shortcut to open the Find and Replace dialog box with the "Replace" tab open? Ctrl+F Ctrl+A Ctrl+H Ctrl+FIND

Ctrl+H

Which key combination can a user press to toggle between formula view and normal view within Excel? Shift-Tab Shift+` Ctrl+` Ctrl-Tab

Ctrl+`

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

Developer

What is the use of 'Icon sets' under Conditional formatting? Displays icons directly in the cells. The icons depend on the cell's value. Applies graphic bars directly in the cells, proportional to the cell's value. Deletes all the conditional formatting rules from the selected cells. Applies background color, proportional to the cell's value.

Displays icons directly in the cells. The icons depend on the cell's value.

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. F1 F2 F3 F4

F3

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

Field

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

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

What does the Excel keyboard shortcut Ctrl+Alt+F9 do? highlights error codes in the current workbook calculates only the formulas in the active worksheet. Other worksheets in the same workbook aren't calculated. forces a complete recalculation of all formulas in all open workbooks. Ctrl+Alt+F9 will not do anything in Excel

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

Which of the following is NOT considered a style attribute? Font Pattern Alignment Formula

Formula

Which of the following options provides correct syntax for the LOOKUP function? LOOKUP(lookup_value,lookup_vector,[result_vector]) LOOKUP(lookup_vector,result_vector) LOOKUP(lookup_value,answers_range) LOOKUP(lookup_value)

LOOKUP(lookup_value,lookup_vector,[result_vector])

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) Comma Style Format Painter Left Align Center Align

Left Align

What is the name of the company that created Excel? Microsoft Google Apple Dell

Microsoft

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

What command is used to *create your own rule* in conditional formatting? New Rule Manage Rule Custom Rule Include Rule

New Rule

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned? range lookup lookup_value row_care_param None of the options provided is correct

None of the options provided is correct

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

Based on the class lecture, which of the following is true about table renaming? Table renaming is not recommended in Excel as it will cause formulas to stop functioning properly Renaming a table is done automatically when a Table is extended (e.g., when you add a new row of data) A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS) Excel tables are named based on the worksheet it is located in, e.g., a table in "Sheet1" will be named "Sheet1Table1" or "Sheet1Table2"

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key) Absolute Relative Frozen Automated

Absolute

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" must appear in the cells surrounded by asterisks (i.e., the * character). The word "budget" can appear anywhere within the cell. It looks specifically for cells containing budget multiplied by some number. For example, a cell containing the formula: "=budget*0.25". Only cells with the text "budget" at the beginning or the end of the cell, are counted. Thus a cell with "budget constraint" is counted, but a cell with "Previous budget estimates" is not counted.

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

Excel formatting tools are used to achieve which of the following? To create programming code in Java/C++ To make the end result attractive and more functional To distract readers from inaccuracies in text and figures The primary use of formatting is to create Excel Pie Charts All of the options listed are valid

To make the end result attractive and more functional

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

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

_______ lookup functions will look up a value in the first column of the lookup table (or range) and return a corresponding value from the specified table column (within the same row). Vertical Horizontal if else Table display

Vertical

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 No, because you will end up with the total for Quarter 1 in that cell No, because you must re-enter the formula for each quarter Answer depends on whether Excel 2007 or Excel 2010 is used

Yes

Is it possible to get the data back after deleting it using keyboard? May be None of the (other) options provided is correct Yes No

Yes

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

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.

What characters are NOT allowed in sheet names ? [] , All options are correct !

[ ]

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? cells_example.xlsx 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

What is the use of the following data validation formula: =A2>A1 ? accepts values for cell A2 that are greater than the value of cell A1 accepts values for cell A2 that are lesser than the value of cell A1 accepts values for cell A2 that are lesser than the value of cell A5 accepts values for cell A2 that are lesser than the value of cell A8

accepts values for cell A2 that are greater than the value of cell A1

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 descending ascending color coded ranged

ascending

Which of the following is an object that contains all other elements in an Excel chart? chart area chart surface chart radar plot area

chart area

HLOOKUP and VLOOKUP are both _________ in Excel functions formatting options interest payment calculation functions style and theme related options

functions

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference valid invalid multi-cell average summation within a

invalid

For a range called Data, what does the function =COUNTIF(Data, "<>0") return? number of cells not equal to 0 number containing a positive value number of cells containing a negative value number of blank cells

number of cells not equal to 0

The range_lookup parameter in VLOOKUP and HLOOKUP function is ________ mandatory optional compulsory fixed

optional

Which of the following chart-related items identifies general trends in your data? trendline timeline chart tools series

trendline

The ____ command is used to display text on multiple lines in a cell. shrink to fit wrap text merge cells icon formatting

wrap text

Which of the following is NOT a tool in Analysis ToolPak for Excel? y-Test t-Test z-Test F-test

y-Test

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? Reading_Symbol Reference.xlsx 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. This is automatically provided to you when you make an Excel Table 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 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

Please refer to the figure. What Excel element is shown in the red box? File button Quick Access toolbar Scrollbar Ribbon

Quick Access toolbar

Please see the attached workbook. The result of the formula =VLOOKUP("Apple",A2:B5,2,FALSE) is which of the following? dataLOOKUP_Ex5.xlsx Color Green Red Fruit

Red

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? Review (tab) -> Comments (command group) -> Next Review (tab) -> Comments (command group) -> Previous Home (tab) -> Navigate (command group) -> Next Comment 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? File button Quick Access toolbar Scrollbar Ribbon

Ribbon

Please see the attached workbook image. What sequence of steps will allow you to format the data into a table? Select data, then click "Ctrl + 1" and choose the "Table Creation"option Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon Select data, then click on conditional formatting command in Excel Select data, then click the "Convert to Table" option under the Table Tools / Design tab

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

What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do? Selects the entire array Selects all graphic objects on the worksheet Selects a rectangular range of cells around the active cell 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 keyboard shortcut: __________. Ctrl+Alt Shift+F2 F5 Ctrl+A

Shift+F2

Which of the following is a shortcut key used to calculate only the formulas in the active worksheet? Shift+F9 all of the choices provided are valid F9 Ctrl+Alt+ F9

Shift+F9

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form? Tab Ctrl+Tab Ctrl+Tab+Enter Shift+Tab

Shift+Tab

Which icon (in data validation error alerts) is used to prevent users from entering invalid data into a cell? Stop Warning Information Prevent

Stop

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

Synchronous Scrolling

Which of the following returns a #REF! error value? MS Excel cannot recognize the text in the formula (it may be written in a foreign language) The formula contains an invalid cell reference Calculation doesn't include all data None of the options provided is correct

The formula contains an invalid cell reference

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 syntax is incorrect, e.g., it should read =COLUMNS(Asset) * ROWS(Asset) The range Asset is a rectangular range of contiguous cells Some cells in the range are empty

The range Asset consists of non-contiguous cells

Which function returns the contents of a cell at the intersection of a specified row and column from a range? INDEX() MATCH() LOOKUP() IFCELL()

INDEX()

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

ISLOGICAL

Which of the following statements is TRUE? The Analysis Toolpak lets you generate random numbers and histograms only, hence is not very useful You use the FREQUENCY() function in Excel to find Fourier wave frequencies The main difference between SUMIF() and COUNTIF() is that the former is used with multiple criteria, but the latter (i.e., COUNTIF() ) only allows a single criteria If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

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

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 the contents and formatting of the cell are removed only the formatting of the cell is removed no changes occur

Only the contents of the cell are removed