final exam

If you accidentally create a formula that divides a number by zero, what error values will you get?

#DIV!/0

Michael estimates he can afford a monthly car payment of $390.00. How much should he pay (Negotiated Sales Price) if he makes a $1,500 down payment?

$23,150.83 Goal Seek: Set cell to the monthly payment to value : 390 by changing : negotiated sales price

Referencing the spreadsheet model and the values shown, which is the monthly car payment Michael will pay?

$489.96

Which of the following is an example of an Absolute Cell Reference?

$F$10 and Interest_Rate(cell C6's 'name') - C$4 mixed cell reference - $F$10 absolute cell ref. - Interest_Rate(cell C6's 'name') is a named cell which is an absolute ref. (named cells and named ranges are always absolute ref.) -AB43 relative cell ref. -$2$F incorrect cell ref. ($F$2 would be a correct absolute cell ref.)

In the previous question, what type of formula is being asked about?

3D- 3D formulas and 3D references are used to reference cells across worksheets. The name 3 D is because a 1) Roe 2)Column , and 3) Worksheet are being referenced

If cell C4 contains the formula =$B$1*C$3 and it is copied right to cell D4 which of the following formulas will appear in cell D4?

=$B$1*D$3

If cell F2 contains the formula =$D$2*$E2 and it is copied down to cell F3 which of the following formulas will appear in cell F3?

=$D$2*$E3

The formula =AVERAGE(B3:C5) returns the same result as which of the following?

=B3+B4+B5+C3+C4+C5/COUNT(B3:C5)

Assume Michael buys a $29,000.00 car and makes a $1,800 down payment (interest and terms remain the same). Use the spreadsheet model to determine how much principal Michael will pay on his fifth car payment.

=CUMPRINC Rate = 3.1% *12 NPER = 5 *12 in years pv = amount financed $27,200 start period = 5 end period = 5 type = 0 $424.05

Assume you have a workbook with three worksheets, JanSales, FebSales, and MarSales. You want to create a Summary worksheet. You add a new worksheet to your workbook. In cell A1 of your new workbook you type a label, "Total Quarterly Sales." In cell A2, you enter a formula to add the values in cell E3 from the three worksheets. Which of the following formulas will appear in cell A2 on the Summary worksheet?

=JanSales!E3+FebSales!E3+MarSales!E3

Michael is purchasing a new car. He created the worksheet shown below to help estimate how much he can afford to spend and to calculate his monthly car payment. Answer the questions that follow. Referencing the spreadsheet model, which of the following formulas should Michael enter in cell C8?

=PMT(C6/12,C7*12,C5) -Rate and number of periods need to be at the same level (month, quarter, year, etc.)

If you reference call E5 located in a worksheet named "Sales" on another worksheet in the same workbook, excel uses which of the following references?

=Sales!E5

A cell's location is determined by the intersection of

A column letter and a row number

In the formula =ROUND(AVERAGE(B1:B10),0), the function _____________ is considered to be nested.

AVERAGE

Which of the following is NOT a cell range:

All are cell ranges A2:B3 two-variables A5, C5, F5 Sheet1:Sheet3!A5:D9

Grouped worksheets can be edited and formatted in this way:

All at the same time

the input values hat an excel function uses to perform operations are called ___

Arguments

the inputs needed for an excel function to perform operations are called

Arguments

To copy a formula or number pattern down a column or across a row which of the following methods could you use?

Auto Fill

The _____ function recognizes both text and number cells.

COUNTA

You can recalculate a worksheet at any time by pressing the F9 function key or by selecting the _____________ button found in the Calculation group on the FORMULAS tab on the ribbon.

Calculate Now

What chart elements are displayed in the following chart?

Chart title, Primary vertical axis title, legend, data labels.

If you want to see a preview of how a worksheet will appear when printed, what do you do?

Click the File tab, and then click Print

Using _____ allows a call to change appearance based on a criteria.

Conditional Formatting

Using ______________ allows a cell to change appearance based on a criteria.

Conditional Formatting

When using an array function you must press ____to make the function calculate correctly.

Ctrl +Shift + Enter

The ____ syntax is used by Excel to indicate that a cell reference is absolute

Dollar symbols

____use a unique set of fonts, colors, and effects to create a consistent image.

Excel themes

Once you set custom page breaks, excel freezes the panes so users cannot change the breaks on the current sheet.

FALSE

The use of Currency-Style or Accounting-style formatting affect the way that numbers are used in calculations.

False

The IPMT function is used to calculate the total interest on a loan.

False - IPMT returns the interest payment for a specified period

Formulas cannot reference values or formulas in calls on other worksheets.

False - you enter formulas that reference across worksheets

You imported the data shown into excel from a text file. Which of the following tools could be used to parse the data?

Flash Fill, Text-to-Columns, and Text functions (like LEFT, RIGHT, FIND, etc.) - in these situations, it always bet to try Flash fill first, text-to-columns second, and finally text functions, if all else fails.

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?

Goal Seek

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.

IF(Ship Method = "Boat", Value *0.15, IF(Ship Method = "Ground", Value * 0.17, Value* 0.22) The following could also be correct answers: IF(Ship Method = "Boat", Value *0.15, IF(Ship Method = "Air", Value * 0.22, Value* 0.17) IF(Ship Method = "Air", Value *0.22, IF(Ship Method = "Boat", Value * 0.15, Value* 0.17) IF(Ship Method = "Air", Value *0.22, IF(Ship Method = "Ground", Value * 0.17, Value* 0.15) IF(Ship Method = "Ground", Value *0.17, IF(Ship Method = "Boat", Value * 0.15, Value* 0.22) IF(Ship Method = "Ground", Value *0.17, IF(Ship Method = "Air", Value * 0.22, Value* 0.15)

It ____ a good practice to hard code values into excel functions rather than entering values in cells and using cell address references.

Is not

Which of the following is not a basic statistical function?

LOOKUP

If you want to balance a title over several columns, what do you do?

Merge and center the data over all columns.

Your boss has given you customer sales data for the last quarter. He asked you to find the largest and smallest values in the data file. Which excel functions might you use?

Min, Max

In solving for the answer to the previous question, which cell was the 'set cell'?

Monthly payment

With a _________ function, you include a function inside another function as one of the arguments.

Nested

Based on the above data range, if the following formula is in call G6, what value will be returned? =IF($F6<=$E6,"Within","Over")

Over - Cell G6: $525.00 is > $500

Which statement about comment boxes in Excel is true?

Position the mouse pointer over the cell containing a comment box indicator to display a comment box contents.

In the preiosu question, the cell that is being referenced in the formula is known as a ____ cell.

Precedent - A precedent cell supplies a value to the cell contain a formula; A dependent cell requires the preceding value in another cell to calculate its result.

An excel function is a

Predefined formula that performs a calcualtion

When a value being referenced in a call is changed, all the formula results using the cell reference ____?

Recalculate

The ___ function adds all the values in a range that meet specified criteria.

SUMIF

Excel is a ____ application.

SpreadSheet

MIN, MAX, RANK, AND QUARTILE.EQ are what type of excel functions?

Statistical

The NOT function takes only one argument and essentially changes a single TRUE value to FALSE, or a single FALSE value to TRUE.

TRUE

You have the below "Shipping Cost" table. The shipping cost is based on the weight of the item. You have used a VLOOKUP function in cell F10 to determine the cost. Which of the following is the correct input for the last argument ([range_lookup]) in this LOOKUP function:

TRUE

which cell becomes active when you press the "Enter" key?

The next cell down

The following formula is entered into cell B4: =OR(5<6,4<7,2<0). The word TRUE appears in cell B4. Why?

There are more true than false arguments. OR is a logical function that tests a number of user-defined conditions and returns. TRUE if ANY of the conditions evaluate to TRUE.

If you change a theme, Excel will update those cells formatted using cell styles.

True

In Backstage view, the general category in the excel options dialog box enables you to your name to personalize Microsoft office.

True

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?

Use Custom Sort

A formula in excel is

a combination of cell references, mathematical operators, values, and/or functions used to perform calculations.

You _____ create and save a customized theme.

can

A bar chart

compares values across categories using horizontal bars

The IF function

evaluates a condition and returns one value if the condition is true and a different value if the condition is false

A chart is composed of only the chart area and labels. You cannot change it, only re-do it after it is created.

false

Once you set custom page breaks, excel freezes the panes so users cannot change the breaks on the current sheet.

false

VLOOKUP searches for a matching value in the leftmost column of the table, and then retrieves the value ____.

in the same row but in another column that you specify

By default all cells are set to be ___ once the worksheet is protected.

locked

The symbols > and >= are examples of ____.

logical operators

Which Excel function would be used to find the middle value in a list of test scores?

median

A circular reference

occurs when a formula directly or indirectly refers to the cell containing the formula

Excel creates charts ____ as the original data set, but you can move the chart another worksheet in the workbook.

on the same worksheet

A "Range"

refers to a group of adjacent or nonadjacent cells

You can use VLOOKUP and HLOOKUP to retrieve data stored in lookup tables on ________.

the same worksheet other worksheets

Excel assigns a serial number to each date entered which allows dates to be treated the same as other numbers in the worksheet.

true

A two-variable data table enables you to analyze ____ input variables and _____ results.

two, unlimited -Also, Know a one-variable enables you to analyze one variable and unlimited results.

a line chart

uses a line to connect data points in order to show trends over a period of time

an excel chart is a

visual representation of numerical data