Upgrade to remove ads
Only $35.99/year

Excel Formulas and Functions

Terms in this set (36)

A1
Both the column and row references are "relative" and will change when the reference is copied and pasted to other cells
$A1
The column reference is "absolute" and will remain constant when copied and pasted to other cells. The row reference is "relative" and will change when copied and pasted to cells in other rows of the worksheet.
A$1
The column reference is "relative" and will change when copied and pasted to cells in other columns in the worksheet. The row reference is "absolute" and will remain constant when copied and pasted to other cells.
$A$1
Both the column and row references are "absolute" and will remain constant when the reference is copied and pasted to other cells.
COUNT
(value1, [value2], ...) - counts the number of cells in a range of cells that contain numbers
COUNTA
(value1, [value2], ...) - counts the number of cells in a range of cells that are not blank
AVERAGE
(number1, [number2], ...) - calculates the simple average of a set of numbers
MAX
(number1, [number2], ...) - returns the largest value in a set of numbers
MIN
(number1, [number2], ...) - returns the smallest value in a set of numbers
AND
(logical1,[logical2],...) - will result in TRUE if all of the combined expressions are TRUE. The AND function returns FALSE if any of the combined expressions are FALSE.
OR
(logical1,[logical2],...) - will result in TRUE if any of the combined expressions are TRUE. The OR function returns FALSE if all of the combined expressions are FALSE
NOT
(logical) - accepts a single Boolean expression as an argument and returns the opposite of that expression. If the Boolean expression evaluates as TRUE then the result of the NOT function is FALSE. Likewise, if the expression evaluates as FALSE then the result of the NOT function is TRUE.
IF
(logical_test,[value_if_true],[value_if_false]) - first is a Boolean expression (logical_test) comparing two values. The other two arguments are used to specify the results of the function depending on the results of the Boolean expression. The second argument (value_if_true) is used to specify the result of the IF function if the Boolean expression result is TRUE. The third argument (value_if_false) is used to specify the result of the IF function is the Boolean expression is FALSE.
COUNTIF
(range,criteria) - allows you to determine the number of cells within a range of cells that contain a specific value
SUMIF
(range,criteria,[sum_range]) - used to calculate the total for a set of values that match a specific criterion. The first argument is the range of cells within which you want Excel to search for a match to the criterion you set. The second argument defines the criterion used to find matches. The last argument defines the range of cells to be included when the total is calculated
AVERAGEIF
(range,criteria,[average_range]) - used to calculate the average for a set of values that match a specific criterion
VLOOKUP
(lookup_value,table_array,col_index_num,[range_lookup])
HLOOKUP
(lookup_value,table_array,row_index_number,[range_lookup]) - The HLOOKUP function assumes that the reference table has been rotated 90 degrees to the right. HLOOKUP will look in the first row of the reference table for a match to the "lookup_value." Excel will then return the corresponding value from the row specified by the "row_index_num" argument as the result of the HLOOKUP function.
TODAY
() - returns the current date
NOW
() - returns the current time
DAY
(serial_number) - returns the day portion of a date (a number between 1 and 31)
MONTH
(serial_number) - returns the month portion of a date (a number between 1 and 12)
YEAR
(serial_number) - returns the year portion of a date (a number between 1900 and 9999)
WEEKDAY
(serial_number, [return_type]) - returns the day of the week for a date
WEEKNUM
(serial_number, [return_type]) - returns the week of the year for a date
LEN
(text) - returns the length, in number of characters, of a block of text
SEARCH
(find_text,within_text, [start_num]) - returns the position of a specific character, word, or phrase within a block of text
LEFT
(text,[num_chars]) - returns a specified number of characters starting from the beginning of a block of text
MID
(text,start_num,num_chars) - returns a specified number of character from the middle of a block of text
RIGHT
(text,[num_chars]) - returns a specified number of characters starting from the end of a block of text
UPPER
(text) - converts a block of text to all upper-case characters
LOWER
(text) - converts a block of text to all lower-case characters
PROPER
(text) - converts a block of text to title-case (the first letter of each new word is capitalized)
CONCATENATE
(text1,[text2],...) - combines blocks of text
SUBSTITUITE
(text,old_text,new_text,[instance_num]) - replaces specified characters, words, or phrases within a block of text with new characters, words, or phrases
PivotTables
PivotTables are used to organize and summarize large amounts of data. PivotTables also allow us to change how we summarize data to look at it in different ways - that's where the name comes from. We can look at the data one way and then easily "Pivot" or change how we examine the data. This allows us to perform some fairly sophisticated analyses quickly and easily
Students also viewed