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)? a) "," (comma) b) ":" (colon) c) ";" (semi colon) d) " " (space)
a) "," (comma)
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. a) #NUM! b) #REF! c) #N/A d) #NAME?
a) #NUM!
Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers? a) 12 b) 8 c) 10 d) 14
a) 12
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 names begin with S? a) =COUNTIF(B2:B23,"S*") b) =COUNTIF(B2:B23,"*S") c) =COUNTIF(B2:B23,"S") d) =COUNTIF(B2:B23,"S?")
a) =COUNTIF(B2:B23,"S*")
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)? a) =COUNTIF(E2:E8,0) b) =SUMIF(E2:E8,0) c) =COUNTIF(E2:E8, Discount=0) d) =SUMIF(E2:E8, Discount=0)
a) =COUNTIF(E2:E8,0)
dataLookup_eg7.xlsx 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? a) =INDEX(I4:K7,MATCH(B2,I4:I7,0),3) b) =INDEX(I4:K7,3) c) =MATCH(B2,I4:I7,0) d) =MATCH(I4:I7,0)
a) =INDEX(I4:K7, MATCH(B2,I4:I7,0),3)
Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet? a) =NOW() b) =DATE() c) =TIME() d) =TODAY()
a) =NOW()
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? Formulas_Test.xlsx a) =SUM(F2:F8) b) =TOTAL(F2:F8) c) =SUMIF(F2:F8) d) =AUTOSUM(F2:F8)
a) =SUM(F2:F8)
dataLookup_eg1.xlsx Please see the attached workbook. The worksheet contains a table of student data (starting in row 13) named as "Students". If you were to use a lookup formulas to get the exact value for salary field in row 2, which of the following formulas would you use? a) =VLOOKUP(C2,Students,4,FALSE) b) =VLOOKUP(C7,Students,4,FALSE) c) =VLOOKUP(C2,Students,3,FALSE) d) =VLOOKUP(C5,employee,4,FALSE)
a) =VLOOKUP(C2,Students,4,FALSE)
Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z? a) AA b) AZ c) ZA d) ZZ
a) 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? 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 sales
a) Add a comment to the sales figure cell, and explain to your assistant what you want him to do
Which of the following types of Cell Formatting can be achieved using the Format Cells feature of Excel? a) All the other options are correct b) Alignment c) Fill d) Protection
a) All the other options are correct
What is the keyboard shortcut to clear the formatting in a cell? a) Alt -> h -> e -> f b) Ctrl -> h -> e -> f c) Shift -> e -> c -> f d) Ctrl -> Shift -> Delete
a) Alt -> h -> e -> f
Which of the following icons/buttons will you click to add or remove gridlines in a chart? a) Chart Elements b) Chart Styles c) Chart filter d) None of these
a) Chart Elements
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? a) Click on any cell containing Salesperson data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button b) Select the entire Salesperson Column, then Click on the Review tab (in the Excel ribbon), followed by clicking on Group and "Sort" c) The Salesperson column cannot be sorted d) Convert the data into a Table, right click on the Table header and select the A-to-Z sort option
a) 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
Which of the following options scrolls the screen so that the active cell is visible? a) Ctrl + Backspace b) Ctrl + * c) Ctrl + [ d) Ctrl + ]
a) Ctrl + Backspace
The Analysis Toolpak can be activated through which sequence of steps? a) File --> Options --> Add ins --> Manage Excel Add-Ins --> Go b) Data --> Data Validation --> Manage Excel Add-Ins --> OK c) Tools --> Options --> Data --> What-if Analysis d) Home --> Ribbon --> Analyse --> Analysis Toolpak
a) File --> Options --> Add ins --> Manage Excel Add-ins --> Go
The HYPERLINK function syntax has two arguments. Which argument has the jump text or numeric value that is displayed in the cell? a) Friendly_name b) Link_location c) Name d) File
a) Friendly_name
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 that ends with 'm' for 'Name'. a) From the Column to Filter list, select Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box b) From the Column to Filter list, select Name. In the right panel, select 'begins with' from the first drop-down list and then type 'm' in the second drop-down list box c) From the Column to Filter list, select Name. In the right panel, select 'equals' from the first drop-down list and then type 'm' in the second drop-down list box d) 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
a) From the Column to Filter list, select Name. In the right panel, select 'ends with' from the first drop-down list and then type 'm' in the second drop-down list box
The HLOOKUP and LOOKUP functions can be found under Formulas (tab)-> _____________. a) Function Library (command group) -> Lookup & Reference b) Function Library (command group) -> Text c) Function Library (command group) -> Financial d) Function Library (command group) -> Logical
a) Function Library (command group) -> Lookup & Reference
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. a) Function Library group (on the) Formulas b) Formulas group (on the) Functions c) Functions group (on the) Insert d) Calculations group (on the) Formulas
a) Function Library group (on the) Formulas
When a row of data is very _______ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows. a) Wide b) Narrow c) Transformed d) Empty
a) Wide
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? a) Yes b) No, because you will end up with the total for Quarter 1 in that cell c) No, because you must re-enter the formula for each quarter d) Answer depends on whether Excel 2007 or Excel 2010 is used
a) Yes
What characters are NOT allowed in sheet names? a) [] b) , c) all options are correct d) !
a) []
Which of the following option is NOT available after right clicking on an Excel worksheet tab? a) changing the font color in the worksheet b) changing the color of the worksheet tab c) hiding the tab d) changing the name of the worksheet
a) changing the font color in the worksheet
Which of the following is an object that contains all other elements in an Excel chart? a) chart area b) chart surface c) chart radar d) plot area
a) chart area
A bar chart is essentially which of the following chart types rotated 90 degrees clockwise? a) column b) line c) area d) surface
a) column
Please refer to the figure. The address of the active cell consists of which of the following? a) column letter, row number b) column height, row width c) column letter d) row number
a) column letter, row number
In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following? a) content other than text (e.g., a date or a number) or a blank cell b) a cell containing data in a numeric or date format c) this function is used primarily for blank or empty cells d) a cell with alphanumeric characters
a) content other than text (e.g., a date or a number) or a blank cell
Example_4.xlsx Please see the attached workbook. The formula in D2 that is copied down the column is "=IFERROR((C2-B2) / C2, "")". Excel displays a / an ____________ string if the formula results any type of error. a) empty b) special character c) alphanumeric d) red circle
a) empty
Please refer to the figure. How many worksheets are contained in the workbook shown? a) 1 b) 5 c) 3 d) A1
b) 5
dataLOOKUP_Ex2.xlsx Please see the attached workbook. What formulas (in cell B3) would return the tax rate for the income in cell B2? a) =VLOOKUP(B2, E1:J3, 3) b) =HLOOKUP(B2, E1:J3, 3) c) =VLOOKUP(B3, D2:F7, 3) d) None of the other options are correct
b) =HLOOKUP(B2, E1:J3, 3)
Which of the following functions can be used to count specific types of errors? a) COUNTA b) COUNTIF c) COUNTBLANK d) COUNT
b) COUNTIF
A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: _______________ a) over-write calculated columns b) delete a record/row c) freeze panes d) create a new table
b) delete a record/row
When the value of a cell is changed (after conditional formatting is applied), the cell formatting _______ based on the conditions set. a) remains unchanged b) is updated c) needs to be manually reset d) usually displays an #ERR message
b) is updated
Based on the class lecture, which of the following is true about table renaming? a) Table renaming is not recommended in Excel as it will cause formulas to stop functioning properly b) Renaming a table is done automatically when a Table is extended (e.g., when you add a new row of data) c) A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS) d) Excel tables are named based on the worksheet it is located in, e.g., a table in "Sheet1" will be named "Sheet1Table1" or "Sheet1Table2"
c) a table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)
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? a) Press Ctrl +X b) Press Ctrl + Z c) Press Ctrl + V d) Press Ctrl + A
d) Press Ctrl + A
Please refer to the figure. What Excel element is shown in the red box? a) File button b) Quick Access toolbar c) Scrollbar d) Ribbon
d) Ribbon
Would you be able to apply the "watch window" option in multiple sheets at the same time? a) This option was available in Excel 2013 but not in Excel 2016 b) Yes, but only if there are no more than 3 sheets in the workbook c) No d) Yes
d) Yes
A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following? a) entity b) component c) element d) cell
d) cell
Which of the following chart types does NOT support the Data Table chart element? a) Treemap b) Stacked Column Chart c) Clustered Column Chart d) Line Chart
a) Treemap
Which formula would you use if you want to accept only text that begins with a specific character 'a' for a given cell 'A1'? a) Use a formula: =LEFT(A1)='a' b) Use a formula: =RIGHT(A1)='a' c) Use a formula: =UPPER(A1)='A' d) None of the options provided is valid
a) Use a formula: =LEFT(A1)="a"
Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column _________ and row __________. a) letter, number b) number, letter c) value, range d) range, value
a) letter, number
Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do? a) pastes values and number formatting b) paste values in color c) pastes values with a percentage formatting d) pastes contents with values and formatting
a) pastes value and number formatting
What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area? a) to prevent blank cells in the copied range from overwriting existing data b) to skip pasting content into the blank cells c) to copy the blank cells from the copied range into the paste area d) none of the above
a) to prevent blank cells in the copied range from overwriting existing data
datavalidation.xlsx Please see the attached workbook. In the Student Rank table, notice that H15 and H19 have the same value. Since the two students cannot have the same student ID, what formula would you use in the Data Validation settings in this case to make sure duplicate entries are not allowed? a) =COUNTIF(H13:H19)=1 b) =COUNT($H$13:$H$19,H19) c) =COUNTIF($H$13:$H$19)=1 d) =COUNTIF($H$13:$H$19,H19)=1
d) =COUNTIF($H$13:$H$19,H19)=1
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? a) Ctrl + N b) Ctrl + E c) Ctrl + W d) Ctrl + B
a) Ctrl + N
Which of the following keyboard shortcuts "cuts" the current selection of cells to both the Windows and Office clipboards? a) Ctrl + X b) Ctrl + V c) Ctrl + C d) Ctrl + Z
a) Ctrl + X
Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default? a) Developer b) Review c) Home d) Insert
a) Developer
True or false: Excel can only import HTML files from your local computer. a) False, Excel can import HTML files from the local computer or the web b) False, Excel can only import HTML files from the web c) True, Excel can only import HTML files from your local computer d) False, Excel cannot import HTML files
a) False, Excel can import HTML files from the local computer or the web
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? a) Home b) Review c) Insert d) Formulas
a) Home
If you want to find the cells on the worksheet that have data validation, which option would you select? a) Home(tab) -> Editing (group) -> Find & Select -> Data Validation b) Home (tab) -> Editing (group) -> Clear c) Formulas (tab) -> Calculation (group) d) Home (tab) -> Styles (group) -> Conditional formatting
a) Home (tab) -> Editing (group) -> Find & Select -> Data Validation
Please examine the attached workbook. The chart was created based on data in cells A1 through C6. If new data is added in cells A7 through C7, will the chart include the new data automatically? PS2_Chart1 a) No b) No, this feature is only supported for Clustered Column charts c) Yes, but only because it is a Stacked Column chart d) Yes
a) No
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? a) Once you have a table, you can click the "Table Tools/Design" tab. Then, under "Table Style Options" check the box "Total Row". b) This cannot be done anymore, it is an unsupported feature in Excel 2013 c) The most effective way is to add a new row, and manually add total options d) Adding a Total Row requires knowledge of Visual Basic which is not covered in this course
a) Once you have a table, you can click the "Table Tools/ Design" tab. Then, under "Table Style Options" check the box for "Total Row"
charts_sampleData Examine the survey results in the attached workbook (in Sheet2). What type of chart would be most suitable to represent the survey response data? a) Pie b) Scatter c) High-Low-Close d) Area (2D or 3D)
a) Pie
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
a) Review (Tab) -> Comments (command group) -> Next
Please refer to the figure. Which of the following is the title of the chart displayed? a) Sales per month b) Sales (in thousands) c) Month d) Series 1
a) Sales per month
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 a) Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose b) Select cells A1 through D3 in Sheet1 -> Copy -> Transpose -> Go to Sheet2 -> Paste c) Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Formulas d) It is only possible if done manually
a) Select cells A1 through D3 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose
Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook? a) Shift + S b) Ctrl + S c) (Excel ribbon tab) File -> Save d) Clicking the Close (X) button in the workbook's title bar
a) Shift + S
Please refer to the figure. Which of the following View ribbon tab options was used to display the horizontal bar between the non consecutive rows containing "Astoria, OR" and "Victoria,TX"? a) Split b) New Windows c) View Side by Side d) Hide
a) Split
examle_COUNTIF.xlsx Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10)) a) The number of cells containing mode within the cell range A1-A10 b) 10 (the value of the mode in cells A1-A10) c) there is insufficient information to answer this question d) 10 (the number of cells in the range A1-A10)
a) The number of cells containing mode within the cell range A1-A10
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? a) The range Asset consists of non-contiguous cells b) The formula syntax is incorrect, e.g., it should be read =COLUMNS(Asset) * ROWS(Asset) c) The range Asset is a rectangular range of continuous cells d) Some cells in the range are empty
a) The range Asset consists of non-contiguous cells
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a time less than a specified time? a) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. b) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than' and enter the end time. c) Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than', and enter the end time d) Home (tab) -> Editing(group) -> Find & Select -> Data Validation
b) Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than' and enter the end time.
What is the shortcut to open the Find and Replace dialog box with the "Find" tab open? a) Ctrl + H b) Ctrl + f c) Ctrl + A d) Ctrl + Alt + F
b) Ctrl + F
charts_example.xlsx Examine the attached workbook. Which ribbon tab provides the option to resize charts? (e.g., change the height & width) a) Layout b) Format c) Design d) Insert
b) Format
What statement about the two Hyperlink arguments Hyperlink(link_location, [friendly_name]) is TRUE? a) Link_location is required, Friendly_name is required b) Link_location is required, Friendly_name is optional c) Link_location is optional, Friendly_name is required d) Link_location is optional, Friendly_name is optional
b) Link_location is required, Friendly_name is optional
Does deleting a cell by pressing the Delete key also delete the conditional formatting on the cell? a) No, it only deletes the cell's style b) No c) Yes, but only if the data is text d) Yes
b) No
Please refer to the figure. What Excel element is shown in the red box? a) File button b) Quick Access toolbar c) Scrollbar d) Ribbon
b) Quick Access toolbar
The ___________ function is one solution to account for floating point number errors. It also serves to round off values to the desired number of decimal places. a) DECIMAL() b) ROUND() c) AVERAGE() d) FLOOR()
b) ROUND()
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? a) None of the (other) options provided is correct b) Synchronous Scrolling c) Side View d) Toggle
b) Synchronous Scrolling
Examine the attached workbook. What conditional formatting rule is applied in cells A1:A5? attachment1 a) Color Scales b) Top/Bottom Rules -> Bottom 10% c) None of the options provided is correct d) Highlight Cell Rules
b) Top/Bottom Rules -> Bottom 10%
dataLOOKUP_Ex1.xlsx 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)? a) HLOOKUP b) VLOOKUP c) TAXLOOKUP d) None of the options provided is valid
b) VLOOKUP
Charts are dynamic, e.g., they change as the underlying data changes. Is this an accurate statement? a) No, they are static by default, and you must press "Ctrl-F7" to update them b) Yes, by default they are dynamic c) No, as this quality would lead to charts with circular references d) Yes, but additional options must be specified to make charts dynamic
b) Yes, by default they are dynamic
You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks? a) passwords are NOT case sensitive b) passwords are case sensitive c) if you forget your workbook password, you can email the TA to retrieve it for you d) workbook passwords automatically expire in 360 days
b) passwords are case sensitive
A group of cells is referred to as which of the following? a) group b) range c) pack d) class
b) range
In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to _____________. a) select a row b) select a column c) select a range d) select multiple rows or columns
b) select a column
Example_5.xlsx Please see the attached workbook. Using the formula =B2/C2 what error is displayed? a) #N/A b) #NULL! c) #DIV/0! d) #NAME?
c) #DIV/0!
Which of the following statements is true? a) A workbook can have at most 3 worksheets b) A worksheet can have many workbooks c) A workbook can have many worksheets d) A worksheet can have at most 3 workbooks
c) A workbook can have many worksheets
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)? a) Add Chart Element > Chart title b) Remove Chart Element > Data Labels c) Add Chart Element > Data Labels d) Remove Chart Element > Chart title
c) Add Chart Element > Data Labels
Which of the following is TRUE regarding the HTML files that Excel creates? a) Excel cannot open an HTML file b) Formulas and charts in an HTML file created from an Excel workbook can be edited c) Changes to graphs and formulas cannot be made to an HTML file created from Excel d) In the HTML files, formulas can be edited, but the charts appear as static graphic images
c) Changes to graphs and formulas cannot be made to an HTML files created from Excel
What option in the Table Tools / Design tab removes a table without losing the data in it? a) Remove Duplicates b) Summarize with PivotTable c) Convert to Range d) Delete Table
c) Convert to Range
worksheet_file1.xlsx worksheet_file2.xlsx Please refer to the attached workbook. 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 in "worksheet_file2.xlsx"? a) C6 b) C7 c) D7 d) D6
c) D7
Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value? a) ISERROR b) ISNA c) ISLOGICAL d) ISERR
c) ISLOGICAL
Reading_Symbol Reference.xlsx 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? 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 select "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)
c) One way to do this is by going through the "Format Cells" dialog, and select "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab
In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50) a) Products!2:I50 b) Canned Fruit & Vegetables c) Products!F2:F50 d) In this type of formula, Excel does not sum up anything, it only checks IF something is true
c) Products!F2:F50
What does selecting the option "Current region" within the: Go To Special Dialog Box (Home(tab) -> Editing (command group) -> Find & Select do? a) Select 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
c) Selects a rectangular range of cells around the active cell
formulas_mixed_cell_references.xlsx Examine the attached Excel file. When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas? a) =$B$7*$F$2 b) =B$7*F2 c) =B7*F2 d) =$B7*F$2
d) =$B7*F$2
IF_formulas.xlsx You may use the attached spreadsheet, "IF_formulas.xlsx" to answering this question. Which of the following IF() functions evaluate as being TRUE? a) =IF("B10>100","B10+10","B10+8") b) =IF(B10>200,"true","false") c) IF() functions can only be used with Product data d) =IF(B10>100,"true","false")
d) =IF(B10>100,"true","false")
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? a) Cell C8 is the wrong cell to calculate the Quarter 2 sales total b) =SUM(B5:E5) c) =SUM(B4:B7) d) =SUM(C4:C7)
d) =SUM(C4:C7)
If you write a lookup formula to look up the text 'education', the formulas considers which of the following a match? a) EDUCATION b) Education c) education d) All answers provided are correct
d) All answers provided are correct
Which of the following file types can Excel import? a) XML Files (.xml) b) Comma separated values (.csv) c) Microsoft Access (.accdb) d) All of the file types
d) All of the file types
How will you refresh the query after moving the cell pointer anywhere within the external data table in the worksheet? a) Right-click and choose Refresh from the shortcut menu b) Choose: Data (tab) -> Connections (command group) -> Refresh All c) Click "Refresh" in the Workbook Connections dialog box (displayed by choosing: Data (tab) -> Connections (command group) -> Connections). d) All the options in this question are valid
d) All the options in this question are valid
If you want to create a rule to highlight cells that contain the word 'good', which option would you select? a) Conditional Formatting -> Highlight Cells Rules -> Between b) Conditional Formatting -> Highlight Cells Rules -> Less than c) Conditional Formatting -> Top/Bottom Rules -> Top 10 items d) Conditional Formatting -> Highlight Cells Rules -> Text that contains
d) Conditional Formatting -> Highlight Cells Rules -> Text that contains
Which of the following is a VALID sequence of options for selecting a different chart layout? a) Design (from Chart Tools) -> Type group b) Design (from Chart Tools) -> Chart Styles c) Insert -> Charts -> Other Charts d) Design (from Chart Tools) -> Chart Layouts
d) Design (from Chart Tools) -> Chart Layouts
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? a) It displays the average of the numbers in the column b) Displays the sum of the values in the column c) 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 d) Displays the number of entries in the column
d) Displays the number of entries in the column
Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query? a) Tables to select from an external database b) Criteria to select specific records from a table c) Sorting of results from a table d) Graphing of data from tables
d) Graphing of data from tables
Which of the following best explains the MATCH function? a) It returns 'True' if an item is present in an array b) It returns 'True' if an item is present in any order in an array c) It returns the relative position of an item in an array d) It returns the relative position of an item in an array that matches a specified value in a specified order
d) It returns the relative position of an item in an array that matches a specified value in a specified order
Which of the following is NOT an advantage of using defined range names to work with ranges in Excel? a) Entering a name is less error-prone than entering a range address b) Formulas become more understandable c) Creating formulas is easier d) Moving to areas of the worksheet is controlled
d) Moving to areas of the worksheet is controlled
Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true? a) If you're looking for information in the worksheet, click the Find tab b) If you want to replace existing text with new text, use the Replace tab c) You can use the Options button to display additional options d) You cannot replace a type of formatting in a cell with another type of formatting
d) You cannot replace a type of formatting in a cell with another type of formatting
Fill in the blank: The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) __________ and ___________. a) range (and) constant b) expression (and) criteria c) constant (and) expression d) range (and) criteria
d) range (and) criteria
