BA 242 Excel Exam 1

DatedIf function (returns the time between two dates)

=DATEDIF()

Now function (today's date and time)

=NOW()

NOT functions

changes true to false, or false to true =NOT(logical1) =NOT(5>6) = T

Left function

returns the specified number of characters from the start of a text string =LEFT()

Absolute reference

$H$6

Mixed Reference: Absolute Column

$H6

Absolute value function (absolute value)

=ABS(range)

AVERAGEIF functions

=AVERAGEIF(range, criteria, [avg range])

COUNTA function (counts the number of cells that contain data)

=COUNTA(range)

CountBlank (counts the number of empty cells, zero is a number)

=COUNTBLANK(range)

COUNTIF functions

=COUNTIF(range, criteria)

Find function

=FIND(), returns the starting position of text string within another text string, is case-sensitive

Logical functions return either a TRUE or FALSE result

=ISLOGICAL(value)

Month function (returns the month of a date, a number between 1 and 12)

=MONTH(cell)

Rand function (returns a random number between 0 and 1)

=RAND()

Round function (rounds up a number to a specified number of digits)

=ROUND(cell, # of digits)

SUMIF functions

=SUMIF(range, criteria, [sum range])

SumProduct function (returns the sum of the products of corresponding ranges)

=SUMPRODUCT(array 1, array 2)

Today function (today's date)

=TODAY()

Year function (returns the year of a date from 1900-9999)

=YEAR(cell)

average function

=average(range)

count function

=count(range)

max function

=max(range)

min function

=min(range)

sum function

=sum(range)

Nested IF functions

For every IF function there are two outcomes 2 IFS, 3 outcomes 3 IFs, 4 outcomes, etc.

Mixed Reference: Absolute Row

H$6

conditional formatting

allows the specification of rules that apply formatting to a cell as determined by the rule outcome

If functions

checks whether a condition is met, and returns one value if true, and another value if false =IF(test, [value if true], [value if false])

AND function

checks whether all arguments are TRUE, and then returns TRUE if all arguments are TRUE =AND(logical1, logical2,)

OR function

checks whether any of the arguments are true, and then returns true or false. Returns false only if all arguments are false. =OR(logical1, logical2)

In order to access multiple worksheets at once, hold ____ and toggle each active worksheet

control

named ranges

group of cells that have been given a name

Concatenate function

joins several text strings into one text string =CONCATENATE()

Vertical Lookup functions

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. Table must be sorted in ascending order =VLOOKUP(lookup value, table array, column number, [range number]

Substitute function

replaces existing text with new text in a text string =SUBSTITUTE()

Index functions

returns a value or reference of a cell at the intersection of a particular row and column, in a given range =INDEX(array, row numb, [column numb])

INDIRECT function

returns the reference specified by a text string =INDIRECT(ref_text, a1)

IfError functions

returns value_if_error if expression is an error and the value of the expression itself otherwise =IFERROR(value, value_if_error)

Proper function

the first letter in each word is converted to uppercase =PROPER()