MIS 112 Final Exam

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)

What is total widget sales for the West Region for the year? $13,282 $5,129 $4,015 None of the above

$5,129

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

formulas_sum_reference.xlsx 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? 14537.45 14875.5 14787.45 9787.45

14787.45

dataLookup_eg7.xlsx Please see the attached workbook. If the formula =MATCH(B2,I4:I7,0) was used in cell B3, what would be the result? 2 3 1 0

2

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)

dataLookup_Ex7.xlsx Please see the attached workbook. The formula:=OFFSET(A1,MATCH("Mary",A2:A5,0),2) returns what value? 101 Name 28 22

22

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

31

What is the result of the following expressions (formulas), respectively?= (10 - 6) * 10 and= 10 - 6 * 10 40 and -50 -50 and 40 40 and 50 50 and 40

40 and -50

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

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell range A1:A10, enables the user to enter a value in A1:A10 only if it's greater than or equal to the value in A1? =A1>=$A$1 =A1>=A1 =A2<=$A$1 =A2>=$A1

=A1>=$A$1

n 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 =B3+C3 =B4*C4 ={B4+C4}

=B4+C4

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? =COUNTIF(B2:B23,"6*?") =COUNTIF(B2:B23,"{6?}") =COUNTIF(B2:B23,"??") =COUNTIF(B2:B23,"??????")

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

Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its decimal equivalent? =HEX2DEC("3BC") =HEXTODEC("3BC") =HEX2DEC(3BC) =HEXTODEC(3BC)

=HEX2DEC("3BC")

dataLOOKUP_Ex2.xlsx Please see the attached workbook. What formula (in cell B3) would return the tax rate for the income in cell B2? =VLOOKUP(B2, E1:J3, 3) =HLOOKUP(B2, E1:J3, 3) =VLOOKUP(B3, D2:F7, 3) None of the other options are correct

=HLOOKUP(B2, E1:J3, 3)

To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. Which of the following is the right option to create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at http://example.microsoft.com. The link text to be displayed is "Quarterly Profit Report". =HYPERLINK("[Example.xlsx]E2", "Quarterly Profit Report") =HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report") =HYPERLINK("[Annual Report.docx]QrtlyProfits", "Quarterly Profit Report") =HYPERLINK("QrtlyProfits", "Quarterly Profit Report")

=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report")

IF_formulas.xlsxYou may use the attached spreadsheet, "IF_formulas.xlsx" to answering this question. Which of the following IF() functions evaluate as being TRUE? =IF("B10>100","B10+10","B10+8") =IF(B10>200,"true","false") IF() functions can only be used with Product data =IF(B10>100,"true","false")

=IF(B10>100,"true","false")

dataLookup_eg4.xlsx Please see the attached workbook. If you have to lookup the supplier of the item C43 in the given array of data, which of the following formulas will you use? =LOOKUP(B1,H2:H7,K2:K7) =LOOKUP(B1,H2:H7,I2:I7) "=LOOKUP(B1,H2:H7,J2:J7) =LOOKUP(B1,L2:L7,K2:K7)

=LOOKUP(B1,H2:H7,I2:I7)

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)

Please examine the attached image. Which of the following formulas will calculate the total number of stores across the four states? =SUM(B2:B5) =COUNT(B2:B5) =NUMBER(A1:B5) =COUNT(B1)

=SUM(B2:B5)

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY =WEEKDAY(A1)=1 =WEEKDAY(A1)=SUNDAY =ISSUNDAY(A1)=1 =ISWEEKDAY(A1)=SUNDAY

=WEEKDAY(A1)=1

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

If you write a lookup formula to look up the text 'education', the formula considers which of the following a match? EDUCATION Education education All answers provided are correct

All answers provided are correct

#NAME? error occurs under which of the following conditions? Formula contains an undefined range or cell name Formula contains text that Excel interprets as an undefined name Formula uses a worksheet function that's defined in an add-in or the add-in is not installed All of the options provided are correct

All of the options provided are correct

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

What is the name of the icon highlighted using the red oval? https://d2l.arizona.edu/content/enforced/810514-277-2194-1MIS112001/Chart_basics.png?_&d2lSessionVal=mok9wKCezuwWvzGLF2vEcoMqX Chart Elements Add charts Chart styles Chart filter

Chart elements

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'

If you want to create a rule to highlight cells that contain the word 'good', which option would you select? Conditional Formatting -> Highlight Cells Rules -> Between Conditional Formatting -> Highlight Cells Rules -> Less than Conditional Formatting -> Top/Bottom Rules -> Top 10 items Conditional Formatting -> Highlight Cells Rules -> Text that contains

Conditional Formatting -> Highlight Cells Rules -> Text that contains

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

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"? C6 C7 D7 D6

D7

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? It displays the average of the numbers in the column. Displays the sum of the values in the column. It displays how many numeric values (i.e., numbers) exist in the column. If the column contains Text values, you need to use a different function. Displays the number of entries in the column.

Displays the number of entries in the column.

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? Format tab (chart tools) --> Reset to Match Style (under the "Current Selection" group) Right click on the chart --> Reset to Match Style Either of the previous options (resetting through the menu or the right-click options work) Home tab --> Wrap Text, followed by, Paste

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

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

F2

Which of the following command sequences will allow you to save a copy of your workbook with a different name? Ctrl, A Ribbon, Save File, Save As Data, Create Copy

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 ) This type of IF() functions with an embedded OR() is no longer supported in Excel 2013. Such IF() statements are too complicated for use in business. They are only used by NASA. 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 Using a nested IF() in a formula is not recommended because the formula result is d

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

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram? Filename (to import Data from Access) Ignore duplicates vs. Permit duplicates Simple vs. Compound Interest Input Range and Bin Range

Input Range and Bin Range

working_with_rules.xlsx Please see the attached workbook. Why does cell B8 lose its current format when you change the data in the cell from 5000 to 500? Cell C8 doesn't lose its format Because rules do not support changes in data Changing data can cause software bugs It is no longer in the top 5 items The rule is designed to highlight values >=5000

It is no longer in the top 5 items

Which keyboard shortcuts can be used to move the active cell one colunn to the left? Left arrow or Shift + Tab Left arrow or Tab Left arrow or Ctrl + Tab Left arrow or Alt + Tab

Left arrow or Shift + Tab

In order to keep track of all conditional formatting used in a worksheet, we use the ___________ menu command under Conditional Formatting. Manage Rules Data Bars Color Scales Icon Sets

Manage Rules

dataLookup_eg9.xlsx Please see the attached workbook. Is =VLOOKUP(E3,TABLE1,3,FALSE) a valid lookup function when used in cell E4? Yes No: because TABLE1 does not exist No: because the table has only two columns but the function refers to column 3 None of the other answers are correct

No: because the table has only two columns but the function refers to column 3

Which type of chart does not allow format changes to the Plot area? Treemap Thermometer chart None of the (other) options provided is correct Bar chart

None of the (other) options provided is correct

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

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." This cannot be done anymore, it is an unsupported feature in Excel 2013 The most effective way is to add a new row, and manually add total options Adding a Total Row requires knowledge of Visual Basic which is not covered in this course

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

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

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE? The formula syntax has an error The formula looks for a supplier with an ID of: *4*, or a product category of Beverages The formula is used to generate a Histogram Two criteria are checked for this formula to include a value in the total:a) the product category must be "Beverages", and b) the supplier must be "4"

Two criteria are checked for this formula to include a value in the total:a) the product category must be "Beverages", and b) the supplier must be "4"

What of the following is FALSE about filtering a table? You can filter by multiple values in a column by using multiple check marks. You can filter a table using any number of columns. You can choose "Text Filters" or "Number Filters" to display only the rows you are interested in. When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

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

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 Narrow Transformed Empty

Wide

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

Can you apply conditional formatting to an Excel table that already has its own formatting? No you cannot No you can only apply to a sheet in the workbook Yes you can Yes you can apply it to a table, but not to multiple tables on your worksheet

Yes you can

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 No, the asterisk or "*" symbol is used to match/find a single character The symbol for a single character is the tilde: ~ One of the limitations of form-based searching is the inability to find/match single characters

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

Which of the following BEST applies to the INDEX function? You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted) It usually returns error codes: #ERR1 - #ERR5 It returns the relative position of an item in an array (e.g., "4") when a match is found It cannot be used with the MATCH function

You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)

Which of the following characters is VALID for use in an Excel workbook file name? _ : " \

_

cells_example.xlsxPlease 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 the cell has an error wrong value / formula in the cell 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 a blinking line to separate two cells a highlighted cell a dynamic line chart where the lines have metallic color effects

a mini-chart that is displayed in a single 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 a question mark a tilde followed by an asterisk a tilde by itself this is a type of search you cannot do with Excel Forms

a tilde followed by an asterisk

When a range of cells is selected, which of the following occurs? the selected cells are all highlighted in the same color none of the cells is highlighted in any color a box marks the boundary of the selected cells, but none of the cells are highlighted all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ . comma (,) semi-colon (;) ampersand (&) colon (:)

ampersand (&)

Using the =NA() function in a cell, makes it clear that data is not _______ and hasn't been ______ accidentally. unavailable, entered available, deleted formulaic, erased none of the options provided is correct

available, deleted

datavalidation2.xlsx Please see the attached workbook. In cell A2, how is the data validation done? by checking if there are no numbers present by checking if there are no special characters such as '#','$','*' etc. by checking if the number of characters is between 4 and 20 by checking to ensure there are at least 5 characters present

by checking if the number of characters is between 4 and 20

In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query. columns records rows sort order

columns

Which of the following does the function =NOW() return? current date current time current date and time nothing, it is not a valid Excel function

current date and time

When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match exact different dissimilar orderly

exact

To make Data Bars easier to view, we can ________ the numerical values in the cell. query hide automatically shrink use bold fonts for

hide

Examine the following VLOOKUP formula '=VLOOKUP(C2,EmpData,2,FALSE)'. What is the name of the argument field represented by C2? lookup_value table_array col_index_num range_lookup

lookup_value

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return? number of cells containing the current date current date cells containing the current date cells not containing the current date

number of cells containing the current date

Data retrieved from Web Queries do not include __________ and contents of __________. text, data text, pictures pictures, scripts text, tables

pictures, scripts

Fill in the blank: The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______. range (and) constant expression (and) criteria constant (and) expression range (and) criteria

range (and) criteria

Which of the following is NOT one of the VLOOKUP function arguments? lookup_value table_array col_index_num row_index_num

row_index_num

The HLOOKUP function searches for data in ______. columns rows external databases external workbooks

rows

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______. select a row select a column select a range select multiple rows or columns

select a column

What does it mean when excel displays the following error? ##### a number is divided by a very large number that is #'d out for privacy the column is not wide enough to display contained data or a negative date or time is used unrecognized text not matching a data validation rule none of the options provided is valid

the column is not wide enough to display contained data or a negative date or time is used

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? to prevent blank cells in the copied range from overwriting existing data to skip pasting content into the blank cells to copy the blank cells from the copied range into the paste area None of the above

to prevent blank cells in the copied range from overwriting existing data

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 Crtl+X Press Ctrl+Z Press Crtl+V Press Crtl+A

Press Crtl+A

In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50) Products!I2:I50 Canned Fruit & Vegetables Products!F2:F50 In this type of formula, Excel does not sum up anything, it only checks IF something is true

Products!F2:F50

The ________ function is one solution to account for for floating point number errors. It also serves to round off values to the desired number of decimal places. DECIMAL() ROUND() AVERAGE() FLOOR()

ROUND()

What does the following formula do? =COUNTIF(Data, TODAY()) Returns the number of cells containing the current date in the range named Data. Returns the number of cells that were last changed on the current date in the range named Data. "Returns the number of cells containing the text ""TODAY()"" in the range named Data." 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? 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

The last step in the Query Wizard, "Finish", lets you do which of the following? Save a query so that you can reuse it Determine the order of sorting the output Choose hyperlinks that will appear for the query Decide whether the query will use relative or absolute cell references

Save a query so that you can reuse it

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

Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook? Shift + S Ctrl + S (Excel ribbon tab) File -> Save clicking the Close (X) button in the workbook's title bar

Shift + S

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

To create a single file web page, what file format does a user have to select from the Save as Type drop-down list? XML data Single File Web Page Web Page Excel Binary Workbook

Single File Web Page

What function removes leading and trailing spaces from a cell? TRIM CUT COMPACT CLEAN

TRIM

Please examine the attached workbook. Which company has the highest income? ( Income = Sales - Expenses ) PS2_Chart1 Amazon Tesco Wallmart Fry's

Tesco

Which of the following statements is TRUE with respect to the VLOOKUP() function? The VLOOKUP() function always looks up a value in the first column of the lookup range. The VLOOKUP() function searches for a value in the top row of a table and returns a value in the same column from a row you specify in the table. To find an exact match, use the VLOOKUP() function with the optional fourth argument set to TRUE. All the other options are correct.

The VLOOKUP() function always looks up a value in the first column of the lookup range.

What part(s) of a table can be selected? An entire column, but not an entire table An entire row, but not an entire column The entire table or an entire row only The entire table, an entire row, or an entire column from a table can be selected

The entire table, an entire row, or an entire column from a table can be selected

Example_5.xlsx Please see the attached workbook. To round off B11 cell to four decimal places what formula would you use? The formula: =AVERAGE(4,B11) The formula: =ROUND(B11,4) The formula: =FLOOR(B11,2,2) The formula: =DECIMAL(B11,4)

The formula: =ROUND(B11,4)

examle_COUNTIF.xlsx Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10)) The number of cells containing the mode within the cell range A1 - A10 10 (the value of the mode in cells A1 - A10) there is insufficient information to answer this question 10 (the number of cells in the range A1 - A10)

The number of cells containing the mode within the cell range A1 - A10

Which of the following functions could be used if you want to display "NOT FOUND" when you lookup for an item that is not part of the lookup_vector? SUM MATCH INDEX IFERROR

IFERROR

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

When (or why) does Excel draw arrows to indicate an error source? For finding dependencies between two images pasted in different worksheets In Tracing Error Values Error-checking the sheet Background Because Functions Must Have Arrows

In Tracing Error Values

external_ex1.xlsx 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 greater than 21 for 'Age' column. From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list From the Column to Filter list, select Age. In the right panel, select 'is lesser than' from the first drop-down list and then select '21.0' from the second drop-down list From the Column to Filter list, select Name. In the right panel, select 'is lesser than' from the first drop-down list and then select '21.0' from the second drop-down list From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then select 'Sam' from the second drop-down list

From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list

Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet? HYPERLINK() SUM() INDEX() MATCH()

HYPERLINK()