Homework answers / question archive / Excel Tutorial 3: Calculating Data with Formulas and Functions   TRUE/FALSE        1)   A constant is a value in a formula that doesn’t change

Excel Tutorial 3: Calculating Data with Formulas and Functions   TRUE/FALSE        1)   A constant is a value in a formula that doesn’t change

Computer Science

Share With

---

 

TRUE/FALSE

 

     1)   A constant is a value in a formula that doesn’t change.

 

 

     2.   Optional arguments are always placed first in the argument list.

 

 

     3.   One challenge of nesting functions is to make sure that you include all of the parentheses.

 

 

     4.   Functions are organized in the Function Library group on the FORMULAS tab.

 

 

     5.   A mixed reference “locks” one part of the cell reference while the other part can change.

 

 

     6.   An error value begins with a pound sign (#) followed by an error name that indicates the type of error.

 

 

     7.   By default, AutoFill copies only the content not the formatting of the original range to the selected range.

 

 

     8.   To display the current date and time, which is updated each time the workbook is reopened,

use the TODAY function.

 

 

     9.   The IF function is a logical function that returns one value if a condition is true, and returns a different value if that condition is false.

 

 

   10.   Lookup tables can be constructed for exact match or approximate match lookups.

 

 

MODIFIED TRUE/FALSE

 

     1.   An argument can be any type of value including text, numbers, cell references, or even other formulas or functions. _______________

 

 

     2.   An error value indicates that some part of a function is preventing Excel from returning a calculated value. ________________

 

 

     3.   When a formula includes a cell reference, Excel interprets that cell reference as being located relative to the position of the current cell. _________________

 

 

     4.   The following mixed reference in the formula, $A5, has an absolute column reference and a relative row reference. _________________________

 

 

     5.   The Fill Formatting Only option, which is the default, copies both the content and the formatting. ______________

 

 

     6.   The statement “A1 >= B1” tests whether the value in cell A1 is lesser than or equal to the value in cell B1. _________________________

 

 

     7.   The WORKDAY function displays the date of the weekday a specific number of weekdays past a starting date.

 

 

     8.   A lookup table organizes numbers or text into categories. _____________

 

 

     9.   A trial-and-error method lets you explore the impact that changing input values has on the

calculated values in the workbook. _________________________

 

 

   10.   Goal Seek automates the trial-and-error process by allowing you to specify a value for

a calculated item, which Excel uses to determine the input value needed to reach that

goal. _________________________

 

 

MATCHING

 

Identify the letter of the choice that best matches the phrase or definition.

a.	NOW	f.	Mon, Tues, Wed, Thurs, Fri
b.	error value	g.	F4
c.	COUNTA	h.	absolute
d.	<	i.	nested
e.	MEDIAN	j.	mixed

 

 

     1.   A Statistical function that calculates the middle value of a collection of numbers

 

     2.   A date and time function

 

     3.   Used to cycle through reference types

 

     4.   Function counts the number of nonblank cells in the specified range

 

     5.   References that are fixed

 

     6.   A comparison operator

 

     7.   AutoFill pattern

 

     8.   Indicate that some part of formula is preventing Excel from returning a calculated value.

 

     9.   A function inside another function

 

   10.   Contains both relative and absolute references.

MULTIPLE CHOICE

 

     1.   The ____ category of Excel functions retrieves data from multidimensional databases involving online analytical processing.

a.	OLAP	c.	Logical
b.	Cube	d.	Text

 

 

 

     2.   The ____ category of Excel functions returns (true-false) values

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

 

 

 

     3.   Which of the following is a Statistical function?

a.	INT	c.	ROUND
b.	RAND	d.	AVERAGE

 

 

     4.   Which of the following is a Math & Trig function? ____

a.	INT	c.	MIN
b.	COUNT	d.	AVERAGE

 

 

 

     5.   =ROUND(AVERAGE(A1:A100),2) is an example of a(n) ____ function.

a.	complex	c.	absolute
b.	logical	d.	nested

 

 

     6.   After you describe a function in the Search for a function box in the Insert Function dialog box, click ____.

a.	Go	c.	Insert
b.	Select	d.	Next

 

 

 

     7.   The numbers, text, or cell references used by the function to return a value are called ____.

a.	expressions	c.	data
b.	arguments	d.	values

 

 

     8.   Referring to the figure above, the last entry shown in Column K is 644.7953813.  There is a black “plus sign” on the lower right edge of the cell that is the ____.

a.	fill handle	c.	formula finder
b.	Auto Fill Options button	d.	function finder

 

 

     9.   Referring to the figure above, the last entry shown in Column K is 644.7953813.  There is an icon in the cell underneath the 644.7953813 which is the ____.

a.	fill handle	c.	formula finder
b.	Auto Fill Options button	d.	function finder

 

 

 

   10.   Referring to the figure above, which number represents the COUNT function?

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

 

 

   11.   Referring to the figure above,  which number refers difference between the scheduled and

attended workouts.

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

 

 

 

   12.   Which of the following is not a comparison operator? ____

a.	=	c.	<<
b.	<=	d.	<>

 

 

 

   13.   In the following formula: =IF(A1="YES", "DONE", "RESTART"), what happens if A1= “NO”?

a.	The formula returns the text “RESTART”.
b.	The formula returns the text “DONE”.
c.	The formula returns the text “NO”.
d.	The formula returns the text “NO, RESTART”.

 

 

 

   14.   Which of the following is not a date function? ____

a.	YEAR	c.	AND
b.	NOW	d.	TODAY

 

 

   15.   The ____ function returns the maximum value in the range.

a.	TOP	c.	MAX
b.	FIRST	d.	MOST

 

 

 

   16.   One of the great advantages of ____ references is that you can quickly generate row and column totals without having to worry about revising the formulas as you copy them to new locations.

a.	cell	c.	absolute
b.	relative	d.	formula

 

 

 

   17.   Absolute references are marked with a ____.

a.	<	c.	$
b.	:	d.	!

 

 

 

   18.   When you copy a formula that contains an absolute reference to a new location, the reference ____.

a.	is updated automatically	c.	becomes bold
b.	does not change	d.	has a dotted outline in its cell

 

 

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

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

 

 

 

   20.   Another name for the middle value is the ____ value.

a.	mode	c.	median
b.	center	d.	relative

 

 

 

   21.   ____ arguments may provide more control over the returned value.

a.	Relative	c.	Optional
b.	Required	d.	Absolute

 

 

 

   22.   FUNCTION(argument1, argument2, ...) is ____.

a.	the syntax of writing optional arguments
b.	an example of a formula
c.	the general syntax of all functions
d.	the general syntax of all formulas

 

 

 

   23.   If an optional argument is not included, Excel assumes a(n) ____ value for it.

a.	default	c.	static
b.	generic	d.	absolute

 

 

 

   24.   Excel functions are organized into ____ categories.

a.	8	c.	11
b.	10	d.	12

 

 

 

   25.   Which of the following is not an Excel function category? ____

a.	Math & Trig	c.	Statistical
b.	Algebra	d.	Text

 

 

 

   26.   Which function counts how many cells in a range contain numbers, where value1, value2, and so forth are text, numbers, or cell references?

a.	SUM	c.	COUNTA
b.	COUNT	d.	AUTOSUM

 

 

 

   27.   In “SUM(number1 [, number2, number3, ...])”, number 1, number 2, and number 3 may be numbers or ____.

a.	letters	c.	symbols
b.	cell references	d.	operators

 

 

 

   28.   Which category of Excel functions returns information about the format, location, or contents of worksheet cells? ____

a.	Location	c.	Reference
b.	Information	d.	Description

 

 

 

   29.   In the formula =INT(AVERAGE(A1:A100)), which would occur first? ____

a.	The average of the values in the range A1:A100 is added to the integer.
b.	The average of the integer value is calculated.
c.	The average of the values in the range A1:A100 is calculated.
d.	The integer portion of the value is extracted.

 

 

 

   30.   When you nest functions, you should have ____.

a.	the equal sign
b.	the same number of left and right parentheses
c.	at least two functions
d.	the correct number of brackets

 

 

 

   31.   The Insert Function button is in the ____ group.

a.	Calculation	c.	Sort & Filter
b.	Function Library	d.	Home

 

 

 

   32.   The Insert Function dialog box has a(n) ____ option.

a.	search	c.	find and replace
b.	formatting	d.	expand/collapse

 

 

 

   33.   The fill handle copies ____.

a.	content only	c.	content and formats
b.	formats only	d.	formats and cell references

 

 

 

   34.   Using the AutoFill Options button, the Fill Without Formatting option copies the ____ but not the formatting.

a.	functions	c.	cell references
b.	content	d.	left-aligns

 

 

   35.   Using AutoFill, what would be the extended series of the initial entry Jan? ___

a.	uary	c.	Kan, Lan, Man, Nan
b.	Feb, Mar, Apr	d.	Jan1, Jan2, Jan3,

 

 

   36.   A logical function is a function that works with values that are either ____.

a.	right or wrong	c.	positive or negative
b.	absolute or relative	d.	true or false

 

 

 

   37.   In the formula =IF(A1=B1, C1, C2), the result will be C2 if ____.

a.	A is equal to B	c.	A1+B1 is equal to C1
b.	A does not equal B	d.	A1>B1 is equal to C2

 

 

 

   38.   ____ references are seldom used other than when creating tables of calculated values such as a multiplication table in which the values of the formula or function can be found in the initial rows and columns of the table.

a.	Absolute	c.	Mixed
b.	Relative	d.	Matched

 

 

 

   39.   =IF(G22= ”x ”, SUM(H22:J22),  “ ”) with display ______ if G22 is not equal to “x”.

a.	a blank cell	c.	dashes
b.	the result of the SUM	d.	False

 

 

   40.   ______ functions find values in tables of data and insert them in another location in the worksheet such as cells or in formulas.

a.	Sum	c.	Lookup
b.	Average	d.	Find

 

 

 

   41.   If the VLOOKUP function cannot find the lookup value, the _________ error value is displayed in the cell.

a.	#VALUE	c.	#ERROR
b.	#N/A	d.	#NULL

 

 

 

   42.   One way to perform a _________ is by changing one or more of the input values

to see how they affect the calculated results.

a.	goal seek	c.	search
b.	gunction	d.	what-if analysis

 

 

 

Case-Based Critical Thinking Questions

 

Case 3-1

Ellen is a recent college graduate. She has decided to open a T-Shirt business and would like to create budget worksheet using Microsoft Excel 2013. She need to keep track of her expenses and income for the year.

 

   43.   Ellen opens Excel 2013 and enters her monthly income for one year beginning with January. To create a series of months, she can ____.

a.	Type = Jan-Dec in cell A1 and then press Enter.
b.	Type January in cell A1 and then drag the filll handle.
c.	Type =YEAR in cell A1 and then press Enter.
d.	Type =DATE(Jan, Dec) in cell A1 and then press Enter.

 

 

 

   44.   Ellen has entered all of her monthly expenses. She needs to create a formula that will deduct her monthly expenses from her monthly income. Since her monthly income is the same amount each month, she uses a(n)____ to identify the cell containing the income value.

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

 

 

 

   45.   Ellen’s spending varies from month to month. She wants to calculate the total  expenses for each month. She wants to see all of the arguments associated with the SUM function so she clicks the ____ then clicks SUM to open the Function Arguments dialog box.

a.	Math & Trig button in the Formula group
b.	Math & Trig button in the Function Library group
c.	Financial button in the Formual group
d.	Financial button in the Function Library group

 

 

 

   46.   Ellen wants to find out what her median monthly spending is. The monthly totals for her spending appear in the range A10:L10. Which of the following is NOT a way to enter the MEDIAN function into her database?

a.	Type =MEDIAN(A10:L10) into an empty cell.
b.	Select the range A10:L10, click the Quick Analysis button, click TOTALS, and then click Median.
c.	Click the More Functions button in the Function Library group on the FORMULAS tab, point to Statistical, and then click MEDIAN.
d.	Click the Insert Function button in the Function Library group on the FORMULAS tab, scroll the Select a function list, then click MEDIAN.

 

 

 

   47.   Ellen is expecting to get a 3% raise next year.  She enters .03 in cell D2 so that she can predict her monthly income for the next year. What function key will make D2 an absolute reference?

a.	F2	c.	F6
b.	F4	d.	F1

 

 

 

Case-Based Critical Thinking Questions

 

Case 3-2

Susan is in charge of the inventory reports for her company.  She will need to find specific inventory items as well as project increases in inventory costs.  To get ready she will create a worksheet and use the Microsoft Excel 2013 Analysis tools.

 

   48.   Susan designs a worksheet with the inventory number, description, quantity, unit cost, reorder point and value.  To find values in tables of data, and insert them in another location in the worksheet such as cells or in formulas, she will use ________.

a.	Max function	c.	CountA function
b.	Lookup function	d.	Sum function

 

 

 

   49.   Susan needs to use a lookup function to find an exact match of an inventory item, so she will set the range_lookup value to _______ .

a.	EXACT
b.	1
c.	TRUE
d.	FALSE

 

 

 

   50.   Susan uses VLOOKUP to retrieve the return value from a vertical lookup table.  When she enters the VLOOKUP function, specifying the lookup value and other criteria, the lookup value cannot be found.  It displays ______ error value in the cell.

a.	#####	c.	#N/A
b.	#Value	d.	Value not found

 

 

 

   51.   Susan wants to make some changes to the worksheet to explore the impact that changing input values has on the calculated values in the worksheet. She can use  ______.

a.	Lookup Analysis	c.	IF conditions
b.	What-If Analysis	d.	nested OR conditions

 

 

 

   52.   Susan wants to automate the trial-and-error process by allowing her to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal. She can use ______.

a.	Data Validation	c.	Goal Set
b.	Goal Seek	d.	If Conditional

 

 

 

COMPLETION

 

     1.   Some cell references are called ____________________ references because they are always interpreted in relation to the location of the cell containing the formula.

 

     2.   ____________________ is a set of rules which specifies how a function should be written.

 

 

     3.   The ____________________ organizes all of the functions by category and allows you to search for functions that perform particular calculations.

 

 

     4.   A(n) ____________________ function is a function that works with statements that are either true or false.

 

 

     5.   The ____________________ is the middle value from a sample.

 

 

     6.   TODAY is an example of a(n) ____________________ function.

 

     7.   A(n) ____________________ reference contains both relative and absolute references.

 

 

     8.   Not all functions have arguments, and some functions have ____________________ arguments, which are not required for the function to return a value, but can be included to provide more control over the returned value.

 

 

     9.   ____________________ is an Excel function category that returns information about the format, location, or contents of worksheet cells.

 

 

   10.   Functions can also be ____________________ or placed inside another function.

 

 

   11.   The small black plus sign in the lower-right corner of a selected cell or range is called the ____________________.

 

 

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

 

 

   13.   ____________________ automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal.

 

 

   14.   The ____________________ function is a logical function that returns one value if the statement is true and returns a different value if the statement is false.

 

   15.   An  ___________________ occurs when the lookup value falls within a range of numbers in the first column of the lookup table.

 

 

ESSAY

 

     1.   Explain what a logical function is and give an example of when you would use one in Excel 2013.

 

 

     2.   Why is it that the TODAY and NOW date functions do not have any arguments?

 

 

     3.   What is the relationship between the Function Arguments dialog box and the Insert Function dialog box?