Deck 8: Using Complex Formulas, Functions, and Tables

Question 1

There are four parts to every formula

Answer 1

False

Question 2

A range reference, such as A12:G30, can be part of a(n) argument

Answer 2

True

Question 3

The TODAY function requires no arguments

Answer 3

True

Question 4

Date & Time functions are available in the Date & Time Library group

Question 5

The COUNT function adds values in a range of cells

Question 6

To insert a(n) absolute cell reference, select the cell you want to use for this purpose, and then press F4.

Question 7

AVERAGE is a common statistical function

Question 8

When a formula contains multiple operators, Excel uses standard algebraic rules to determine which calculation to perform first.

Question 9

In a complex formula, addition and subtraction are performed before multiplication and division.

Question 10

All date and time functions produce recognizable text values that you can display "as is" in your worksheets.

Question 11

When you create worksheets that contain many calculations, you rarely need to create formulas that contain more than one mathematical operator.

Question 12

The Quick Access gallery provides easy access to common formatting tools

Question 13

Functions are prewritten formulas that come with Excel

Question 14

The formula =C6+D6+E6-F6 is an example of a complex formula.

Question 15

Once you've written a complex formula, you can drag it to adjacent cells using the fill handle pointer.

Question 16

When you copy a formula from one cell to another, Excel automatically adjusts the cell references in the copied formula to reflect the new formula location.

Question 17

Calculations in parentheses are always evaluated last.

Question 18

An absolute cell reference contains a # symbol before the column letter and row number.

Question 19

Formula AutoComplete is a feature that offers a list of valid functions and names beginning with the letter that you type following the = sign. 

Question 20

Functions save time and help ensure accuracy, and they are available for both simple calculations and extremely complex ones.

Question 21

Excel allows you to analyze a separate range of worksheet data in a(n) ____.

A) table
B) argument
C) pie chart
D) graph

Question 22

Which of the following is NOT a conditional formatting option?

A) Color Scales
B) Icon Sets
C) Sparklines
D) Data Bars

Question 23

The _________ gallery contains popular conditional formatting options like data bars and color scales.

A) Conditional Formatting
B) Quick Analysis
C) Cell Styles
D) Quick Formatting

Question 24

____ formulas perform more than one calculation at a time.

A) Simple
B) Complex
C) Working
D) Function

Question 25

An Excel table is similar to a table in a ____because you can sort data in much the same way.

A) Word document
B) chart
C) database
D) gallery

Question 26

In the accompanying figure, which item displays where the result of the function will appear?

A) 1
B) 2
C) 3
D) 4

Question 27

In the accompanying figure, which item is the name of a function?

A) 1
B) 2
C) 3
D) 4

Question 28

In the accompanying figure, which item represents a description of the function?

A) 1
B) 2
C) 3
D) 4

Question 29

The accompanying figure is an example of ____.

A) using themes
B) conditional formatting
C) sorting
D) filtering

Question 30

When you are ready to specify criteria in the list of available filters, you click the _____ check box to first remove all of the existing check marks.

A) None
B) (Select All)
C) Remove Check Marks
D) Reset

Question 31

When you choose from a list of available filters for a column, you are specifying the ____ you wish to use to filter the data.

A) criteria
B) values
C) order
D) data

Question 32

In the accompanying figure, which item displays the Date & Time button?

A) 1
B) 2
C) 3
D) 4

Question 33

To change the way a page breaks, in the Page Break Preview, drag the ____ dotted line.

A) blue
B) black
C) red
D) white

Question 34

Custom Sort is available by clicking the Sort & Filter button on the ____ tab.

A) HOME
B) SORT
C) DATA
D) INSERT

Question 35

What are the four parts to every function?

A) an equal sign, the function name, a set of parentheses, and variables
B) an equal sign, the function name, a set of brackets, and arguments
C) an equal sign, the function name, a set of parentheses, and arguments
D) a plus sign, the function name, a set of parentheses, and arguments

Question 36

In the accompanying figure, the Total Sales Less Returns column has ___ applied to its cells.

A) Gradients
B) Color Bars
C) Borders & Shading
D) Color Scales

Question 37

When you are ready to convert a range of cells to a table, select the range, and then click the Format as Table button in the ____ group.

A) Format
B) Data
C) Styles
D) Tables

Question 38

You can apply _____ formatting to individual cells based on certain conditions being met.

A) automatic
B) conditional
C) thematic
D) temporary

Question 39

To display only the data you need, you can use the ____ feature.

A) filter
B) sort
C) criteria
D) analysis

Question 40

To make a range of cells stand out visually, you may want to apply ____ and borders.

A) patterns
B) sticky notes
C) hatch marks
D) shading

Question 41

Describe how dates are calculated using serial values.

Question 42

As in database tables, Excel table columns are often called ____.

A) cells
B) fields
C) data sets
D) records

Question 43

The COUNT function counts the number of cells in a range containing ____.

A) numbers
B) conditional formatting
C) formulas
D) mathematical operators

Question 44

The Quick Analysis button appears whenever you select a range of ____ or more cells.

A) 1
B) 2
C) 3
D) 4

Question 45

____________________ are all of the information a function needs to perform a task.

Question 46

Which of the following is not part of a function?

A) =
B) arguments separated by commas
C) [ ]
D) function name

Question 47

The ____________________ function counts the number of cells in a range containing numbers.

Question 48

In the accompanying figure, what is another type of conditional formatting that could be applied to the Total Sales Less Returns column?

A) Icon Sets
B) Sparklines
C) Data Boxes
D) Blends

Question 49

Excel ____________________ let you manage and analyze rows of data that have the same kind of information.

Question 50

You can identify the smallest value in a range of cells using the ____________________ function.

Question 51

The ____ function returns the current date.

A) CURRENT DATE
B) TIME & DATE
C) DATE
D) TODAY

Question 52

Rows of data in an Excel table are called ____.

A) sets
B) table rows
C) fields
D) records

Question 53

List the six common categories of functions in Microsoft Excel.

Question 54

Formulas that contain more than one operator are called ____________________ formulas.

Question 55

The AVERAGE ____ lets you calculate the average of a range of cells.

A) function
B) formula
C) operator
D) criterion

Question 56

You can identify the largest value in a range of cells using the ____________________ function.

Question 57

If your Excel table contains a large amount of data, you might want to ____ it to display only the data you need.

A) sort
B) update
C) filter
D) organize

Question 58

Describe how Microsoft Excel computes formulas containing more than one operator.

Question 59

You can sort up to ____ levels in the Sort dialog box.

A) three
B) five
C) six
D) twelve

Question 60

A(n) ____________________ cell reference is a cell reference that always stays the same, even when you copy a formula that contains it to a new location.

Question 62

You are working on a spreadsheet that contains many different kinds of calculations. Your supervisor knows that Excel has built-in functions for many of the calculations.
Your spreadsheet contains a list of dates and you need to determine the intervals between the dates in the list. Which set of functions should you use to determine how many days have passed?

Question 63

You are working on a spreadsheet that contains many different kinds of calculations. Your supervisor knows that Excel has built-in functions for many of the calculations.
Your spreadsheet contains a set of test results, and your supervisor needs to know information such as the average, maximum, and minimum. Which set of functions should you use in your spreadsheet?

Question 64

You have a spreadsheet containing the sales figures for every sale that has taken place in your organization. The spreadsheet includes the sales person's name, the customer's name, the date of the sale, the amount of the sale, as well as the highest and lowest priced items from the sale.
How can you change the color of the cells that contain values under one dollar in the lowest priced column?

Question 65

You have a spreadsheet containing the sales figures for every sale that has taken place in your organization. The spreadsheet includes the sales person's name, the customer's name, the date of the sale, the amount of the sale, as well as the highest and lowest priced items from the sale.
Your supervisor has asked you to print out a version of the spreadsheet that only contains sales that are greater than $25,000.00. How can you accomplish his request?

Question 66

You have a spreadsheet containing the sales figures for every sale that has taken place in your organization. The spreadsheet includes the sales person's name, the customer's name, the date of the sale, the amount of the sale, as well as the highest and lowest priced items from the sale.
Your supervisor would like to see the sales figures arranged by total sales and then sales person. How can you rearrange the figures to accomplish her request?