Homework answers / question archive / Chapter 15: Working with Formulas and Functions   TRUE/FALSE        1)  A relative reference is always interpreted in relation to the location of the cell containing the formula

Chapter 15: Working with Formulas and Functions   TRUE/FALSE        1)  A relative reference is always interpreted in relation to the location of the cell containing the formula

Computer Science

Share With

---

 

TRUE/FALSE

 

     1)  A relative reference is always interpreted in relation to the location of the cell containing the formula.

 

 

     2.   In Excel, relative references have a $ before each column and row designation.

 

 

     3.   Use relative references when you want to repeat the same formula with cells in different locations on your worksheet.

 

 

     4.   The AVERAGE, COUNT, and MEDIAN functions are examples of statistical functions.

 

 

     5.   Formulas provide the greatest flexibility for working with data that changes.

 

 

     6.   After you select a cell or range, the Live Copy handle appears in the lower-right corner of the selection.

 

 

     7.   Fill Formatting Only is the default AutoFill option.

 

 

     8.   The = symbol in a logical function is a comparison operator.

 

 

     9.   In compound interest, the interest is equal to a percentage of principal for each period that the money has been lent.

 

 

   10.   The PMT function calculates the amount of a monthly loan payment, based on rate, nper, and pv.

 

 

MODIFIED TRUE/FALSE

 

     1.   The formula =SUM(D28:D30) uses a(n) absolute reference.

 

 

     2.   $B$8 is a(n) absolute cell reference.

 

 

     3.   To cycle through the different reference types, select the desired reference and press the F4 key until you have reached the type you want.

 

 

     4.   The reference E$4 is an example of a(n) hybrid reference.

 

 

     5.   The MIDPOINT function calculates the middle value of a collection of numbers.

 

 

     6.   Functions can be placed inside other functions, or nested.

 

     7.   To use AutoFill, you select a cell or range and then drag the fill handle over the cells you want filled.

 

     8.   A(n) determinate function works with statements that are either true or false.

 

     9.   The TODAY function returns the current date and time.

 

 

   10.   The PMT function has two optional arguments: fv and type.

MULTIPLE CHOICE

 

     1.   Most Excel workbooks are created to ____.

a.	build a presentation	c.	provide a focus of thought to a complex task
b.	record and analyze data	d.	collect and organize lists

 

 

 

     2.   The formula =SUM(A1:D1) is an example of a(n) ____ reference.

a.	relative	c.	linear
b.	absolute	d.	tabular

 

 

     3.   Cell references that remain fixed when a formula is copied to a new location are called ___ references.

a.	static	c.	mixed
b.	absolute	d.	locked

 

 

 

     4.   A $ in front of all column and row designations in a formula indicates the formula is composed of ____.

a.	absolute references entirely	c.	mixed references
b.	relative references entirely	d.	some absolute and some relative references

 

 

 

     5.   You use the ____ key(s) to cycle through all available reference types for a selected cell or range.

a.	Alt+R	c.	Windows
b.	F4	d.	Shift+PgDn

 

 

 

     6.   In the reference $C14, column C is a(n) ____ reference and row 14 is a(n) ____ reference.

a.	fixed, absolute	c.	absolute, relative
b.	relative, mixed	d.	mixed, mixed

 

 

 

     7.   Use ____ references when you want different formulas to refer to the same cell.

a.	relative	c.	absolute
b.	mixed	d.	linear

 

 

 

     8.   Every function begins with a set of rules, or ____, which specifies how the function should be written.

a.	logic	c.	syntax
b.	arguments	d.	conditions

 

 

 

     9.   ____ in a function are numbers, text, or cell references used by the function to return a value.

a.	Syntax	c.	Arguments
b.	Conditions	d.	Logistics

 

 

 

   10.   ____ arguments are not required for a function to return a value, but can be included to provide more control over how Excel calculates the returned value.

a.	Optional	c.	Conditional
b.	IF	d.	Descriptive

 

 

 

   11.   COUNTA is an example of a ____ function.

a.	Series	c.	Statistical
b.	Math & Trig	d.	Database

 

 

 

   12.   RAND is an example of a ____ function.

a.	Cube	c.	Statistical
b.	Math & Trig	d.	Logical

 

 

 

   13.   When nesting functions, make sure the number of left and right ____ match.

a.	operands	c.	parentheses
b.	carats	d.	conditions

 

 

 

   14.   If a formula contains several functions, Excel starts with the ____ function and then moves ____.

a.	outward, inward	c.	right, left
b.	left, right	d.	inward, outward

 

 

 

   15.   After you select a function in the Insert Function dialog box, the ____ opens.

a.	function bar	c.	Conditional Arguments dialog box
b.	Function Arguments dialog box	d.	cell/range

 

 

   16.   In the figure above, ____ is a required argument.

a.	B23:M23	c.	38,574
b.	2886	d.	38574

 

 

 

   17.   In the figure above, box ____ points to the value that will be displayed in the active cell.

a.	1	c.	6
b.	4	d.	7

 

 

   18.   In the figure above, box ____ points to the value that will be returned by the function.

a.	1	c.	6
b.	4	d.	7

 

 

   19.   When typing a function directly into a cell, you first type ____.

a.	FUNC	c.	(
b.	$	d.	none of the above

 

 

 

   20.   To insert a function in the active cell, you type in the first few letters of the function, then ____ its function name.

a.	double-click	c.	right-click
b.	click	d.	drag and drop

 

 

 

   21.   ____ copies content and formats from a cell or range into an adjacent cell or range.

a.	AutoFill	c.	The Formula Extender
b.	Drag-and-drop	d.	The Rangefinder

 

 

 

   22.   ____ can extend a series of numbers, patterned text, and dates into an adjacent cell or range.

a.	AutoFill	c.	The Formula Extender
b.	Drag-and-drop	d.	The Rangefinder

 

 

 

   23.   After you select a cell or range, a ____ appears in the lower-right corner of the selection.

a.	ScreenTip	c.	fill handle
b.	toolbar	d.	ranging pole

 

 

 

   24.   The AutoFill Options button gives you the choice to do all of the following EXCEPT ____.

a.	copy cell content and formatting	c.	fill only cell formatting
b.	extend the data series	d.	cycle through relative, absolute, and mixed referencing

 

 

 

   25.   AutoFill can extend a series of ____.

a.	dates	c.	patterned text
b.	times	d.	all of the above

 

 

 

   26.   To create a series with a complex pattern, you use the ____ dialog box.

a.	AutoFill Options	c.	Series
b.	Patterns	d.	Rangefinder

 

 

 

   27.   ____ value is the amount that each numeric value in a series increases over the previous entry.

a.	Stop	c.	Lift
b.	Step	d.	Dock

 

 

 

   28.   A logical function works with statements that are ____.

a.	numerical	c.	open-ended
b.	either true or false	d.	intangible in nature

 

 

 

 

   29.   In the figure above, the operator <= belongs to box ____.

a.	2	c.	5
b.	4	d.	6

 

 

 

   30.   In the figure above, the operator <> belongs to box ____.

a.	1	c.	4
b.	3	d.	6

 

 

 

   31.   A(n) ____ operator is a symbol that indicates the relationship between two values.

a.	introductory	c.	contrasting
b.	comparison	d.	statistical

 

 

 

   32.   The IF function is a(n) ____ function.

a.	algebraic	c.	comparative
b.	logical	d.	intuitive

 

 

 

   33.   All of the following are date and/or time functions supported by Excel EXCEPT ____.

a.	WEEKDAY	c.	HOLIDAY
b.	DAY	d.	NOW

 

 

 

   34.   A(n) ____ function is a function related to monetary calculations.

a.	financial	c.	statutory
b.	investment	d.	fiscal

 

 

 

   35.   In a loan, the ____ is the amount of money being loaned.

a.	interest	c.	principal
b.	RV	d.	down payment

 

 

 

   36.   Interest is calculated as either ____ interest or ____ interest.

a.	accrued, expected	c.	annual, daily
b.	simple, compound	d.	initial, extended

 

 

 

   37.   With ____ interest, the interest is applied not only to the principal but also to any accrued interest.

a.	simple	c.	compound
b.	extended	d.	annual

 

 

   38.   The ____ function calculates the amount of a monthly loan payment.

a.	LOAN	c.	NPER
b.	MLP	d.	PMT

 

 

 

   39.   The ____ argument of the loan payment function is optional.

a.	fv	c.	pv
b.	nper	d.	rate

 

 

 

   40.   For most mortgages and loans, the payment period is ____.

a.	5 years	c.	1 day
b.	30 years	d.	1 month

 

 

 

Case-Based Critical Thinking Questions

 

Case 15-1

Answer the following questions based on this figure:

 

 

 

   41.   The months in row 19 were the result of AutoFill. Which of the following procedures would have been able to accomplish this?

a.	cells B19, C19, and D19 typed in, then fill handle dragged to cell L19	c.	cell A19 typed in, then AutoFill button double-clicked
b.	cell B19 typed in, then cell right-clicked, “Fill to” clicked, “L” entered	d.	none of the above would accomplish this

 

 

 

   42.   Of the following, cell ____ is most likely a calculated field.

a.	F20	c.	K33
b.	I20	d.	I29

 

 

 

   43.   Cell B33 was typed in, then AutoFill was used to complete cells C33:L33. In order for the new values to be calculated correctly, the original formula in cell B33 must have used ____ references.

a.	linear	c.	relative
b.	absolute	d.	logical

 

 

 

   44.   Assume that cell B32 is a summation of cells B24:B31. Assume then that cell B33 is equal to cell B32 minus cell B23. Changing the value in cell B28 and then recalculating will change ____.

a.	none of the other cells	c.	all cells in rows 32 and 33
b.	cells B32 and B33 only	d.	cell B32 only

 

 

 

Case-Based Critical Thinking Questions

 

Case 15-2

In the following questions, determine what type of function is being shown.

 

   45.   =IF(A$31>$F$12,$F11,100)

a.	Date & Time	c.	Logical
b.	Math & Trig	d.	Statistical

 

 

 

   46.   =INT(AVERAGE(C3:C14))

a.	Statistical	c.	Math & Trig
b.	Logical	d.	multiple types nested

 

 

 

   47.   =MAX(B2:B60)

a.	Math & Trig	c.	Logical
b.	Conditional	d.	Statistical

 

 

 

Case-Based Critical Thinking Questions

 

Case 15-3

Refer to the figure below to answer the following questions:

 

 

 

   48.   What is the PV?

a.	$20,000	c.	0.06
b.	6.0%	d.	0.50%

 

 

 

   49.   How would one best make the Monthly Payment figure a positive number?

a.	Subtract the number by itself twice.	c.	Instead of beginning the PMT function with an equal sign, begin it with a plus sign.
b.	Enter a minus sign directly before the PMT function.	d.	Perform a series of what-if analyses on the values until the result becomes a positive $386.66.

 

 

 

   50.   Is the fv shown, and what is it?

a.	yes, $20,000	c.	no, .06
b.	no, 0	d.	yes, 12

 

 

 

COMPLETION

 

     1.   A(n) ____ reference is always interpreted in relation to the location of the cell containing the formula.

 

 

     2.   Cell references that remain fixed when a formula is copied to a new location are called ____ references.

 

 

     3.   A(n) ____ “locks” one part of a cell reference while the other part can change.

 

 

     4.   ____ are numbers, text, or cell references used by a function to return a value.

 

 

     5.   There are a total of ____ categories of Excel functions.

 

 

     6.   Functions placed within other functions are said to be ____.

 

 

     7.   After you become familiar with a function, it can be faster to type the function directly in a cell rather than using the ____ dialog box.

 

 

     8.   Be sure to end all functions with a closing ____ to ensure that Excel interprets the formula correctly.

 

 

     9.   AutoFill can be used with cells containing text, ____, or formulas.

 

   10.   ____ is the default AutoFill option.

 

   11.   To extend a series, you must provide enough information for AutoFill to identify the ____.

 

   12.   The = in a logical function is a(n) ____ operator.

 

   13.   The TODAY function contains ____ arguments.

 

   14.   To determine how much of your monthly loan payment is used for repaying the principal, use the ____ function.

 

   15.   The fv argument of the PMT function stands for ____.

 

MATCHING

 

 

a.	$B17	g.	$C$12:$D$14
b.	type	h.	IF function
c.	syntax	i.	nper
d.	fill handle	j.	A5:A7
e.	simple	k.	compound
f.	logical function	l.	AutoFill

 

 

     1.   an absolute reference

 

     2.   a function that returns one value for a true statement and another value for a false one

 

     3.   any function that works with statements that are true or false

 

     4.   a mixed reference

 

     5.   interest that is applied not only to principal but also to accrued interest

 

     6.   a required PMT function argument

 

     7.   can extend patterned text into an adjacent selection

 

     8.   interest that is equal to a percentage of principal for each period that money is lent

 

     9.   a set of rules

 

   10.   an optional PMT function argument

 

   11.   a relative reference

 

   12.   appears after you select a cell or range

 

ESSAY

 

     1.   In what general situations should you use each of the three kinds of references mentioned in the chapter? Give a brief example of each as well.

 

     2.   The chapter listed six different financial functions, and situations in which you would want to use them. List and briefly describe four of these.

 

 

     3.   Name and briefly expand on the four guidelines given in the chapter for planning and presenting a budget.