Excel Function Flashcards
is the most widely used cell reference in formulas in Excel.
relative reference
is indicated in your spreadsheet by a $ sign around the cell
absolute reference
absolute cell reference that allows the row reference to change, but not the column reference
$A1
absolute cell reference that allows the column reference to change, but not the row reference
A$1
absolute cell reference that allows neither the column nor the row reference to change- this is the default one
$A$1
is a predefined formula already available in Excel and perform specific calculations in a particular order based on the specified values, called arguments, or parameters
functions
function use to add up all the values
sum
function use to get the average of values
average
function use to get the remainder
mod
function use to get the product of all values
product
function use to get the quotient of two numbers
quotient
formulas in Excel get the largest and smallest value in a set of numbers, respectively
max and min
function to compare numbers to other numbers in the same list
rank
3 arguments for the rank function
number, ref, order
In case you want to take values from two or more cells and combine them into one cell
concatenate
To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis
today and now
function deals only with those cells that contain numbers
count
function counts all cells thatare not blank, whether they contain numbers, dates, times, text, logical values of TRUE and FALSE, errors or empty text strings (“”):
counta
is one of the most popular functions in Excel, and it allows you to make logical comparisons between a value and what you expect
if function
relational operators
Less than (<)
Greater than (>)
Equal (=)
Less than equal (<=)
Greater than equal (>=)
Not equal (<>)
Boolean operators
AND
OR
advanced functions in excel
COUNTIF
SUMIF
AVERAGEIF
is used for counting cells within a specified range that meet a certain criterion, or condition
countif function
defines one or several cells to count
range
defines the condition that tells the function which cells to count. It can be anumber,text string,cell referenceorexpression
criteria
is a function to sum cells that meet a single criteria
sumif function
syntax of sumif function
=SUMIF (range, criteria, [sum_range])
function in Excel calculates the average (arithmetic mean) of all the cells that meet a specified criteria
average if function
syntax of averageif function
AVERAGEIF(range, criteria, [average_range])
syntax of countif function
COUNTIF(range, criteria)
