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()