is320 exam

counting functions

"Criteria" can include logical operators (>,<,<>,=) (these are treated as "text" and wildcards (*,?) for partial matching

error message when you attempt to use a cell thats been deleted

#REF

Filtering

-Filtering makes it possible to view specific data. -The filtering function can be applied to only one data set on a worksheet. -Filtering enables a user to examine and analyze a subset of records.

headers and footers can include

3 sections

If cell B7 contains a value that is greater than 20, and "ORDER" will be displayed when the logical test is TRUE, which of the following contains the correct IF function syntax?

=IF(B7>20, "ORDER", "OK")

INDEX function syntax

=INDEX(array, row_number, [column_number]),,,array row_number

INDEX function syntax

=INDEX(array,row_number,[column_number]) array, row

SUMIF Function syntax

=SUMIF(range,criteria,[sum_range])

COUNTA function

A function that returns the number of cells in the selected range that contain text or values, but not blank cells.

NOT a common error message? A) #NPV? B) #NAME? C) #REF! D) #VALUE!

A) #NPV

The ________ function is used when the lookup_value argument checks the top row of the table_array. A) HLOOKUP B) VLOOKUP C) RLOOKUP D) TLOOKUP

A) HLOOKUP

_______ functions enable evaluation and choices to be integrated into an Excel spreadsheet. A) Logical B) Comparison C) Decision D) Conditional

A) Logical

conjunction functions

AND, OR and NOT

NOT a financial function? A) PMT B) PPMT C) NPV D) APR

APR

Which of the following is the correct syntax when using the AND function within the IF function? A) =IF((B3<5 AND C1>7),"Pass","Fail") B)=IF(AND(B3<5,C1>7),"Pass","Fail") C) =IF(AND(B3<5 OR C1>7),"Pass","Fail") D)=AND(IF(B3<5,C1>7),"Pass","Fail")

B) =IF(AND(B3<5,C1>7), "Pass", "fail")

Statistical functions which is NOT an example? A) COUNTIFS B) SUMIF C) AVERAGEIF D) AVERAGEIFS

B) SUMIF

Which of the following is NOT a common logical function? A) IF B) NA C) OR D) NOT

B)NA

VLOOKUP range_lookup argument has the ________ value entered, an exact match with the lookup_value argument is required. A) YES B) NO C) TRUE D) FALSE

C

conditional aggregate function statements are true except: A) Conditional aggregate functions are functions that consolidate or summarize a subset of data that has been filtered based upon one or more criteria. B) With an aggregate function, a range would still be provided, but the aggregation would only use cells that meet a given criteria. C) Elements such as named ranges and cell references should go inside quotes. D) The criteria can be on the data that is being aggregated or on associated data.

C) Elements such as named ranges and cell references should go inside quotes.

COUNT()

Counts the numbers in a range

the keyboard shortcut to move to cell A1 is _____

Ctrl + Home

Error message when you attempt to use a cell thats been deleted as a function argument? A) #NPV? B) #NAME? C) #REF! D) #N/A

D) #N/A

Conditional aggregate function example : A) =IF(AND(C7>B3, D8<=5),"PAY","NO PAY") B) =IF(OR(C7>B3, D8<=5),"PAY","NO PAY") C) =IF(NOT(C7>B3),"PAY","NO PAY") D) =IF((SUM(A1:A7)>7),"PAY","NO PAY")

D) =IF((SUM(A1:A7)>7),"PAY","NO PAY")

All of the following statements are TRUE about the IF function EXCEPT: A) The [value_if_false] argument may be omitted from the IF function. B) The IF function is the most common logical function. C) The TRUE argument of the IF function is optional. D) Cell references listed in the IF function must be capitalized

D) Cell referenced listed must be capitalized

The ________ function returns the number of workdays between two dates. A) WEEKDAY B) WORKINGDAYS C) DAYDIF D) NETWORKDAYS

D) Network days

conjunctive functions statements are true EXCEPT: A) The AND function requires all arguments to be TRUE. B) The NOT function results in the opposite or reverse of a logical statement. C) The OR function requires at least one of the arguments to be TRUE. D) The AND and OR functions cannot be used in the same IF function

D) the AND and OR functions cannot be used in the same IF function

double clicking

Format painter button allows you to use it multiple times

match_type is 0

MATCH finds the first value exactly equal to lookup_value. lookup_array does not need to be sorted

match_type = 1

MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.

match_type is -1

MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order

argument

The inputs used by an Excel function are called

VLOOKUP function statements are true EXCEPT: A) The HLOOKUP function is the more commonly used than the VLOOKUP function. B) The "V" in VLOOKUP stands for vertical and is used when your comparison values are located in a column or vertically to the left of the data that you want to find. C) You can use to look up a value and then, using that value as a reference, return data that is associated with that value. D) VLOOKUP functions are extremely valuable when working with tables where the data is in columns.

a

All of the following statements are TRUE regarding slicers EXCEPT: A) Slicers were introduced in Excel 2007 as an interactive way to filter PivotTable data. B) In Excel 2013, slicers can now also be used to filter data in Excel tables, query tables, and other data tables. C) One of the benefits to using slicers is that they are easy to generate and use. D) Slicers indicate the current filter so you will know exactly what data you are viewing.

a)

If you wanted to determine how many cells in a range are not empty, you would use the ________ function. A) COUNTA B) NOTEMPTY C) COUNT D) COUNTBLANK

a) Counta

The second argument of the IF function is ________. A) [value_if_true] B) [value_if_false] C) [logical_test] D) [criteria]

a) [value_if_true]

The ________ filter displays the values in the field that can be toggled on and off through the use of check boxes. A) advanced B) enhanced C) standard D) additional

a) advanced

Data that is manually calculated is known as ________ data. A) static B) dynamic C) retrieved D) generated

a) static

first argument of the AVERAGEIFS function is : A) average_range B) criteria_range C) criteria D) logical_test

average_range

Table can be created by selecting a range on an Excel worksheet, clicking the ________ tab, clicking the Format as Table button, and selecting a table style. A) Home B) Insert C) Data D) Design

b

Click ________ in the Sort & Filter group on the Data tab to removed any existing filters that have been applied to a data set on the Excel worksheet. A) Delete B) Clear C) Remove D) Cancel

b) clear

The following function is an example of a(n) ________ function. =IF(SUM(H2:J7)>Total_Sales,.05,"No Commission") A) integrated B) complex C) detailed D) compound

b) complex

First argument of the AVERAGEIF function is : A) average_range B) range C) criteria D) logical_test

b) range

The ________ argument of the COUNTIF function lists the cells that will be counted. A) array B) range C) span D) series

b) range

MID(text, k, m)

begins at character k of a text string and returns the next m characters

VLOOKUP function below, which of the following statements is NOT true? =VLOOKUP(A6,Shifts,5,FALSE) A) The content of A6 is compared to the contents of first column of the table array. B) Shifts is the name assigned to the table array being used by the function. C) The number in the third argument stands for how many columns exist in the table array. D) The contents of the fourth argument requires an exact match with the first argument.

c

_______ variables are fields within a data set that can be used to categorize or group for comparison. A) Aggregate B) Category C) Grouping D) Summary

c

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

c) Ctrl

Table can da all of the following except : A) automatically adjust table formatting and formula references B) provide both flexibility and scalability C) automatically sort data in table columns D) help provide context to the user by organizing data in a meaningful way

c) automaticcally sort data in table columns

All of the following statements are TRUE regarding raw data EXCEPT: A) Raw data may be numeric or text. B) Raw data without context is meaningless to users. C) Information is another name for data. D) Raw data is unorganized

c) information is another name for data

The ________ function will run calculations on data that is in a subset when a filter is applied. A) SUM B) TOTAL C) SUBTOTAL D) SUMMARY

c) subtotal

SEARCH(text B, text A, k)

case insensitive

FIND(text B, text A, k)

case sensitive

to remove conditional formatting from a range of cells, after selecting the range of cells

click Conditional Formatting, point to Clear Rules, and select Clear Rules from Selected Cells

VLOOKUP function , 3rd argument is

colum#

TEXT(cell, "format code")

convert a numeric value into a text string by applying the "format code"

VALUE(text)

convert text string (if it represents number) into number

______ variables are data that can be aggregated by summing, counting, or averaging. A) Aggregate B) Category C) Grouping D) Summary

d

Information

data that has context, meaning, and relevance

DATEDIF function

finding the time between two dates

alt+enter

insert a line break or hard return in a cell

conditional formatting

lets you specify formatting for a cell range on the basis of the contents of the cell range.

Criteria argument of the COUNTIF function

logical statement that determine which cells to count

vlookup()

looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.

MATCH function

looks for a value within a range and returns the position of that value within the range

Data sets

name used for a collection of related sets of info that are composed of seperate elements

Match and index functions

provide added flexibility of multiple data ranges that can be located throughout an excel spreadsheed

ColLRow#

relative addressing

CLEAN(cell)

remove some (not all) invisible ASCII characters

FIND()

returns starting position of one string within another text string

CHAR(number)

returns the ASCII character indentiried by the number

LEFT(text,k)

returns the first k characters in a text string

RIGHT(text,k)

returns the last k characters in a text string

match function

returns the relative position (in a range/table) of a cell corresponding to a specified value

CTRL

select a noncontiguous range of cells by pressing and holding it when using it in combination with other keys or mouse

COUNTBLANK function

to count cells that are blank

IFERROR function

tool for detecting an error and displaying something more user friendly than an error message

Pseudo code

tool used to understand logic of conjunctive functions where words are entered in the function structure

Conditional formatting allows you to dynamically change the appearance of cells so that it adds information to your worksheet

true

the dollar sign ($) and comma (,) are stored as part of a numeric cell value

true

Nested IF FUNCTION

uses IF functions as arguments within another IF function