Acis 2504 Chapter 3 Formulas And Functions

Front 1

Text String

Back 1

data stored as a text is commonly referred to as a _______ example "Commission"

Front 2

Named Range

Back 2

Sales_Amount

Front 3

Math Functions include:

Back 3

ABS(Number) - absolute value

INT (Number) - round down to nearest integer

ROUND(Number, Num_Digits) - rounds to a specified number of digits

Front 4

Absolute Value

Back 4

ABS(number)

Front 5

Round down to nearest integer

Back 5

INT(Number)

Front 6

Round to a specified number of digits

Back 6

ROUND(Number,Num_Digits)

Front 7

Returns the mean from a set of numbers

Back 7

AVERAGE(Number1,[Number2],...)

Front 8

Counts the number of cells in a range that contain numbers

Back 8

COUNT(Value1,[Value2],...)

Front 9

Counts the number of cells in a range that are not empty

Back 9

COUNTA(Value1,[Value2],...)

Front 10

Counts the number of empty cells in a range

Back 10

COUNTBLANK(range)

Front 11

Returns the number in the middle of a set of numbers

Back 11

MEDIAN(Number1,[Number2],...)

Front 12

Returns the largest number from a set of numbers

Back 12

MAX(Number1,[Number2],...)

Front 13

Returns the smallest number from a set of numbers

Back 13

MIN(Number1,[Number2],...)

Front 14

Returns the value that occurs the most often within a set

Back 14

MODE.SNGL(Number1,[Number2],...)

Front 15

Returns the number that represents the date in Microsoft Excel date-time code

Back 15

DATE(Year,Month,Day)

Front 16

Returns the time unit specified between two dates, including the two dates (inclusive)

Back 16

DATEDIF(Date1,Date2,Interval)

Front 17

Returns the day of the month, a number from 1-31

Back 17

DAY(Serial_Number)

Front 18

Returns the month, a number from 1-12

Back 18

MONTH(Serial_Number)

Front 19

Returns the computer system date

Back 19

TODAY()

Front 20

Returns the hour in a time value, from 0 to 23

Back 20

HOUR(Serial_Number)

Front 21

Returns the minute in a time value, from 0 to 59

Back 21

MINUTE(Serial_Number)

Front 22

Returns the number of seconds in a time value, from 0 to 59

Back 22

SECOND(Serial_Number)

Front 23

Returns the computer system date and time

Back 23

NOW()

Front 24

Joins text1, text2.....textn together in order into a single string value

Back 24

CONCATENATE(text1,text2,...)

Front 25

Finds find_text in within text. The search begins at start_num (start_num defaults to 1) and IS CASE SENSITIVE. starting position of find_text is returned. If the find_text is not present, an error will occur.

Back 25

FIND(find_text,within_text,[start_num])

Front 26

Returns a string num_chars long from the left side of text. Num_chars defaults to 1

Back 26

LEFT(text,[num_chars])

Front 27

Returns a number that represents the number of characters

Back 27

LEN(text)

Front 28

Returns a string extracted from text beginning in position start_num that is num_chars long

Back 28

MID(text,start_num,num_chars)

Front 29

Returns a string num_chars long from the right side of text

Back 29

RIGHT(text,[num_chars])

Front 30

Returns text with any leading, trailing, or extra spaces between words removed

Back 30

TRIM(text)

Front 31

Cell A5 contains the name "William Morgan". We want to seperate the first names from the customer names displayed in the first column. Basically, we want a different column to say "William". What NESTED function do we use?

Back 31

=TRIM(LEFT(A5,FIND(" ",A5)))

Front 32

Suppose we want to separate the last names from the customer names displayed in the first column. Basically, we want a different column to say "Morgan" from "William Morgan" in cell A5. What NESTED function do we use?

Back 32

Right(A5,(LEN(A5)-LEN(D5)-1))

Front 33

Finds lookup_value in the top row of table_array and returns a value from row_index

Back 33

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

Front 34

Returns a value from array by indexes specified as row_num and column_num

Back 34

INDEX(array,row_num,[column_num])

Front 35

Finds lookup_value in lookup_array

Back 35

MATCH(lookup_value,lookup_array,[match_type])

Front 36

Finds lookup_value in the first column of table_array and returns a value from col_index_num

Back 36

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Front 37

Financial function that calculates periodic payment for a loan based on a constant interest rate and constant payment amounts

Back 37

PMT(rate,nper,pv,[fv],[type])

Front 38

Part of PMT function that represents # of periods over which payments will be made (number of payments to be made)

Back 38

Nper

Front 39

Part of PMT function that represents the Present value - the original value of the loan

Back 39

PV

Front 40

Part of PMT function that represents future value - the value of the loan on the maturity date

Back 40

FV

Front 41

Part of PMT function that represents: 1 if payment is made at the beginning of period; 0 or omitted if payment is made at end of period

Back 41

Type (either 1 or 0)

Front 42

Returns one of two values, depending upon whether the logical statement is evaluated as being

Back 42

IF(logical_test,(value_if_true),(value_if_false])

Front 43

Returns a specified value if a function or formula returns an error; otherwise, it returns the value of the function or formula

Back 43

IFERROR(value,value_if_error)

Front 44

Returns true if logical 1, logical 2,... logicaln all return time

Back 44

AND(logical1,logical2,...)

Front 45

Returns true if any one of logical1, logical2,...logicaln return true

Back 45

OR(logical1,logical2,...)

Front 46

IF function with nested AND

Back 46

=IF(AND(logical1,logical2,...),[value_if_true],[value_if_false])