Multiple choice

example of an absolute reference

$F$10 or interest rate(cell C6's 'name') because named cells and names ranges are always absolute references

example of a relative cell reference

AB43

What is a table?

An excel range with row/column labels

Which of the following formulas could be used to calculate shipping costs in the following situation: When parts are shipped by boat, the shipping cost is 15% of product value; when parts are shipped using ground transportation, the shipping cost is 17% of product value; finally, when parts are shipped by air, the shipping cost is 22% of product value. a. MATCH(Ship Method , 0.15, 0.17,0.22) b. IF(Ship Method = "Boat", Value *0.15, IF(Ship Method = "Ground", Value * 0.17, Value* 0.22) c. IF(OR(("Boat", "Ground", "Air"),Value *0.15, Value * 0.17, Value*0.22)) d. IF(VLOOKUP(Ship Method, "Value", 2, FALSE)*Value)

B

Which of the following CANNOT be performed on grouped worksheets sharing similar structures? A) Entering column and row headings B) Applying conditional formatting C) Entering formulas D) Entering functions

B. applying conditional formatting

Formulas cannot reference values or formulas in cells on other worksheets a. true b. false

B. false

example of mixed cell reference

C$4

What type of reference is used to reference cells across worksheets? a. Relative b. 2D c. 3D

C. 3D because a 1) row, 2) column, and 3) worksheet are being referenced

If the value in a cell that is being referenced is changed, the cell is called ? a. dependent b. independent c. precedent

C. precedent. the precedent cell supplies a value to the cell containing a formula a dependent cell requires the preceding value in another cell to calculate its result

A(n) ________ appears when a user makes a validated cell active and prompts a user before data is entered with information about data constraints. A) Data Validation - list validation B) Data Validation - Error Message C) Data Validation - cell validation D) Data Validation - Input Message

D. input message

what is a structured table?

a table with a defined structure, style, and organizations created by: 1. home tab>style group>format as table 2. insert tab>tables group>table button

Which of the following is the argument in =SUM(J2:J4) A. J2:J4 b. SUM c. = d. All the above are arguments None of the above are arguments

a. J2:J4

An excel function is a a. predefined formula that performs a calculation b. cell reference or a value c. list of values and defined names d. set of rules that govern the structure and components of a formula

a. a predefined formula that performs a calculation

The IF function's first argument is the logical_test and requires which of the following: a. logical operator b. true value c. single cell reference

a. logical operator

When a value being referenced in a cell is changed, all the formula results using the cell reference a. recalculate b. reformat c. return value errors

a. recalculate

When worksheet sources are changed pie chart percentages and pie slices are automatically a. recalculated b. upsized c. displayed d resized (multiple answers)

a. recalculated d. resized

MIN, MAX, RANK, and QUARTILE.EQ are what type of excel functions? a. statistical b. logical c. financial

a. statistical

Which of the following are the same axis for an excel chart? a. category axis and x-axis b. value axis and x-axis c. value axis and y-axis d. category axis and y-axis

b&d value axis and x-axis, category axis and y-axis

If you accidentally create a formula that divides a number by zero, which of the following error values will you get? a. ##### b. #DIV!/0 c. #N/A d. #Ref

b. #DIV!/0

The IPMT function is used to calculate the total interest of a loan a. True b. False

b. False IPMT returns the interest payment for a specified period

In excel, rows are added.. a. below the selected row b. above the selected row

b. above the selected row

The use of currency-style or accounting-style formatting affect the way that numbers are used in calculations a. true b. false

b. false

a 3-D named range can be defined in the name box a. true b. false

b. false it is defined in formula bar as you build it

Which feature in excel greatly increases user efficiency by enabling actions such as data entry to be performed on multiple worksheets at one time? a. linking b. grouping c. bridging d. merging

b. grouping

The _____ chart type is the best chart to illustrate data changes over a period of time a. pie b. line c. area d. column

b. line

pivot tables _______when changes are made to the underlying data in the source a. update automatically b. do not update automatically

b. not update automatically

What of the following is true about hard-coded values? a. A formula such as =P1*Q2 contains a hard-coded value. b. Hard-coding makes it easier to update data and adapt to change. c. In the formula =J5*12 the 12 is a hard coded value. d. Hard-coding is recommended when you have a value that may change.

c. In the formula =J5*12 the 12 is a hard coded value.

Excel stores dates as a serial number, day 1.0 is a. January 1, 1901 b. January 1,1800 c. January 1, 1900

c. January 1 1900

You want to add a visual element to a data set that highlights values that are greater than $200.00, what tool might you use? a. IF function b. Sparkline chart c. conditional formatting d. cell styles

c. conditional formatting

Which of the following is NOT a benefit of using PivotTables? A) Workings with a very large table of data B) Looking for trends C) Consolidating multi-worksheets of data D) Summarizing data

c. consolidating multi-worksheets of data

Pressing ________ allows you to select multiple criteria on a slicer. A) Alt B) Ctrl + Alt C) Ctrl D) Ctrl + Shift

c. ctrl

What excel process allows you to keep rows/columns visible onscreen at all times even when you scroll through a large data set? a. worksheet lock b. data validation c. freeze pane

c. freeze pane

a predefined formula is a ... a. filter b. criteria c. function d. argument

c. function

The structure and order of a function and its arguments are also known as the function's___________ a. reference b. pseudo-code c. syntax d. range

c. syntax

A two-variable data table enables you to analyze ________input variables and ________ results a. one, one b. two, one c. two, unlimited

c. two input variables and unlimited results

What if analysis data table

created to analyze one or two variables in a spreadsheet module; one of the What If data tools

The following formula is entered into cell B4: =OR(5<6,4<7,2<0). The word TRUE appears in cell B4. Why? a. OR is a Boolean function b. OR returns the opposite result c. All arguments are true d. At least one argument is true e. There are more true than false arguments

d. at least one argument is true Or returns true if ANY of the conditions are true

Michael estimates he can afford a monthly car payment of $390.00. He needs to know how much he should pay (Negotiated Sales Price) if he makes a $1,500 down payment. Which of the following Excel tools can help him determine the negotiated sales price for his new car? a.Data Table b.Scenario Manager c.Group and Subtotal d.Goal Seek e.Solver

d. goal seek

what feature of excel greatly increases user efficiency by enabling actions such as data entry and formatting on multiple worksheets at one time? a. merging b. linking c. bridging d. grouping

d. grouping

What is the purpose of the MATCH function? a. it is used as an argument in an INDEX function b. it is an excel lookup function c. it can be used when HLOOKUP or VLOOKUP are not appropriate d. it provides the relative positon of a value in a list of values that meets a specified criteria e. it returns the cell reference at the intersection if a row/column

d. it provides the relative position of a value in a list of values that meets a specified criteria

A one-variable data table enables you to analyze ________input variables and ________ results a. one, one b. two, one c. two, unlimited d. one, unlimited

d. one input variable and unlimited results

Data mining techniques such as PivotTables can detect _______of data a. groups b. categories c. styles d. patterns

d. patterns

You have a list of students attending a meeting (Last names in column A and First names in column B). You want to sort the list by last name, then by first name. Which of the following could you do? a. Sort column A (A to Z) then sort column B (A to Z) b. Sort column A (A to Z) c. Sort column B (A to Z) d. Use Custom Sort e. Use Multiple Column (A to Z)

d. use custom sort

After you import date into excel from a text file, what are the best ways to parse the data?

flash fill, text-to-columns, and text functions like (left, right, find, etc.)