Microsoft Excel - Advanced Excel Formulas & Functions Flashcards
What is the typical formula syntax in Excel?
First is the function name and then the arguments surrounded by parentheses.
What are the two reference types in Excel?
There are relative and fixed type cell references.
Relative allow the reference to change as formula is copied elsewhere.
Fixed refernces are denotes by a $ symbol in the row, column, or both and prevent the reference from changing.
What does the Evaluate Formula option do and how do you use it?
The Evaluate formula shows the result of each step or calculation in a formula; you access it by:
go to the Formulas tab;
then go to the Formula Auditing section;
then go to the Evaluate Formula option.
What do AND() statements accomplish and what is their syntax?
AND() statements evaluate various conditions and return TRUE only if all of the conditions are TRUE.
The syntax is AND(condition1, condition2, etc.).
What do OR() conditions accomplish and what is their syntax?
OR() conditions evaluate various conditions and return TRUE if any one of them is TRUE.
Their syntax is OR(condition1, condition2, etc.)
What do NOT() statements accomplish and what is their syntax?
NOT() statements negate the result of the condition being evaluated.
Their syntax can be either NOT(condition) or “<>”.
What is the IFERROR() function for and what is the syntax?
The IFERROR() returns a specified value if the formula or value being evaluated results in an error.
The syntax is IFERROR(value or formula, value_if_error).
What are some of the most common IS statements?
ISBLANK
ISNUMBER
ISTEXT
ISERROR
ISEVEN
ISODD
ISLOGICAL
ISFORMULA
What are some of the common statistical functions?
COUNT()
AVERAGE()
MEDIAN()
MODE()*
MAX()
MIN()
PERCENTILE()*
STDEV()*
VAR()*
*(expand)
What does the function MODE() return and what is the syntax?
The MODE() function returns the most frequently ocurring or repetitive value in an array or range.
The syntax is MODE(number1, number2,…)
What does the PERCENTILE() function return and what is the syntax?
The PERCENTILE() function returns the k-th percentile of values in a range.
The syntax is PERCENTILE(array, k).
What does the STDEV() function return and what is the syntax?
The STDEV() function estimates standard deviation based on a sample.
The syntax is STDEV(number1, number2,…).
What does the VAR() function return and what is the syntax?
The VAR() function estimates variance based on a sample.
The syntax is VAR(number1, number2,….).
What do the SMALL() and LARGE() functions return and what is the syntax?
SMALL() returns the k-th smallest number in a data set and the syntax is SMALL(array, k).
LARGE() returns the k-th largest number in a data set and the syntax is LARGE(array, k).
What do the RAND() and RANDBETWEEN() functions return and what is the syntax?
The RAND() function returns a random number between 0 and 1 and the syntax is RAND().
The RANDBETWEEN() function returns a random whole number between the specified top and bottom numbers and the sytanx is RANDBETWEEN(bottom, top).
What does the SUMPRODUCT() function accomplish and what is the syntax?
The SUMPRODUCT() function returns the sum of products of corresponding ranges.
The syntax is SUMPRODUCT(array1, array2,…).
What do COUNTIF(), SUMIF(), AND AVERAGEIF() functions return and what is the syntax?
The COUNTIF(), SUMIF(), and AVERAGEIF() functions return the following:
- COUNTIF() returns the number of cells in a range that meet a given condition and the syntax is COUNTIF(range,criteria)
- SUMIF() adds the cells specified by a given condition or criteria and the syntax is SUMIF(range, criteria, sum_range).
- AVERAGEIF() finds the average for the cells specified by a given condition and the syntax is AVERAGEIF(range, criteria, average_range).
What do COUNTIFS(), SUMIFS(), AND AVERAGEIFS() functions return and what is the syntax?
What do COUNTIFS(), SUMIFS(), andAVERAGEIFS() functions return the following:
- COUNTIFS() counts the number of cells specified by various criteria and the syntax is COUNTIFS(criteria, range, criteria…).
- SUMIFS() adds the cells specified by various criteria and the syntax is SUMIFS(sum_range, criteria_range, criteria,…)
- AVERAGEIFS() finds the average for the cells specified by various criteria and the syntax is AVERAGEIFS(average_range, criteria_range, criteria,…).
How do you assign a name to a cell or range.
Select the cell or range and type in the name box the desired name:
What do the ROW() and ROWS() function return and what is the syntax?
The ROW() function returns the row number of a given reference and the syntax is ROW([refernce]).
The ROWS() function returns the number of rows in an array and the syntax is ROWS(array).
What does the VLOOKUP () function do in Excel and what is the syntax?
The VLOOKUP() function searches VERTICALLY for a given value within a specified array and returns a corresponding value from said array.
The syntax is VLOOKUP(given_value, array, column index number, [range_lookup].
Search is done in the LEFTMOST column of the look up array and value returned is the top most occurrence.
What does the HLOOKUP () function do in Excel and what is the syntax?
The HLOOKUP() function searches HORIZONTALLY for a given value within a specified array and returns a corresponding value from said array.
The syntax is VLOOKUP(given_value, array, row index number, [range_lookup].
Search is done in the first row of the look up array and value returned is the left most occurrence.
What does the INDEX() function do and what is the syntax?
The INDEX() function returns the value of the specified cell where the range, row, and column are given.
The syntax is INDEX(range, row, column).
What does the MATCH() function do and what is the syntax?
The MATCH() function returns the position of a given value within a one dimensional array; and the syntax is:
MATCH(value, array, [match_type])
What is the XLOOKUP() function for and what is the syntax?
The XLOOKUP() function retrieves values from a range by matching a lookup value with more flexibility than VLOOKUP() or HLOOKUP(); the syntax is:
XLOOKUP(lookup_value, range, return array[, if not found] [, match mode] [, search mode])
What does the CHOOSE() function do and what is the syntax?
The CHOOSE() function selects a value from a list based on a given index number; the syntax is:
CHOOSE(index, value_1[, value_2…]).
What is the OFFSET() function and what is the syntax?
The OFFSET() function returns the value of a cell within an array or a specific range of cells; the syntax is:
OFFSET(reference, rows, columns[, height][, width])
What does the COUNTA() function do and what is the syntax?
The COUNTA() function gives the number of non-blank cells in a range and the syntax is:
COUNTA(range)
What does the TRIM() funtion do and what is the syntax?
The TRIM() funtion trims all leading and trailing spaces form a given string; the syntax is:
TRIM(cell, value, or range)
What does the UPPER() funtion do and what is the syntax? LOWER() PROPER()
The UPPER() funtion converts al characters to upper case of a given string; the syntax is:
UPPER(cell, value, or range)
What does the LOWER() funtion do and what is the syntax? PROPER()
The LOWER() funtion converts al characters to lower case of a given string; the syntax is:
LOWER(cell, value, or range)
What does the PROPER() funtion do and what is the syntax?
The PROPER() funtion converts all words of a given string to proper, wher all words begin with an upper case letter and the rest are lower case; the syntax is:
PROPER(cell, value, or range)
What does the CONCATENATE() or & function do and what is the syntax?
The CONCATENATE() or & function combines combines text, values, formulas into a single text; the syntax is:
CONCATENATE(Text1, Text2,…) or use the & symbol to unite each text.
What is does the LEFT() function do and what is the syntax?
The LEFT() function return the specified number of leftmost characters in a string; and the syntaxt is:
LEFT(text, [num_chars])
What is does the MID() function do and what is the syntax?
The MID() function returns a number of spcified number or characters from the middle of a string; and the syntaxt is:
MID(txt, start_position, number_of_characters.)
What is does the RIGHT() function do and what is the syntax?
The RIGHT() function returns the specified number of rightmost characters from a string; and the syntaxt is:
RIGHT(text, [num_chars])
What is does the LEN() function do and what is the syntax?
The LEN() function returns the length of a string; and the syntaxt is:
LEN(text)
What does the TEXT() function do and what is the syntax?
The TEXT() function converts a value into text and assigns a particular format; and the syntax is:
TEXT(value, format_text)
What does the VALUE() function do and what is the syntax?
The VALUE() function converts a text into a numeric value; and the syntax is:
VALUE(text)
What does the SEARCH() function do and what is the syntax?
The SEARCH() returns the positionof a characterf at which a specific character is found; and the syntax is:
SEARCH(find_text, within_text[, start_no])
What does the FIND() function do and what is the syntax?
The FIND() returns the position of a characterf at which a specific character is found and is case sensitve; and the syntax is:
FIND(find_text, within_text[, start_no])
What does the SUBSTITUTE() function do and what is the syntax?
The SUBSTITUTE() function X; and the syntax is:
SUBSTITUTE()
What does the TODAY() function do and what is the syntax?
The TODAY() returns the current date; and the syntax is:
TODAY()
What does the NOW() function do and what is the syntax?
The NOW() returns the current date and time; and the syntax is:
NOW()
What does the YEAR() function do and what is the syntax?
The YEAR() function returns the year value of a given cell; and the syntax is:
YEAR(cell)
What does the MONTH() function do and what is the syntax?
The MONTH() function returns the month value of a given cell; and the syntax is:
MONTH(cell)
What does the DAY() function do and what is the syntax?
The DAY() function returns the day of a given cell; and the syntax is:
DAY(cell)
What does the HOUR() function do and what is the syntax?
The HOUR() function returns the hour of a given cell; and the syntax is:
HOUR(cell)
What does the MINUTE() function do and what is the syntax?
The MINUTE() function returns the minute of a given cell; and the syntax is:
MINUTE(cell)
What does the SECOND() function do and what is the syntax?
The SECOND() function returns the second of a given cell; and the syntax is:
SECOND(cell)
What does the EOMONTH() function do and what is the syntax?
The EOMONTH() function calculates the last day of given month; the syntax is:
EOMONTH(start_date, months)
What does the YEARFRAC() function do and what is the syntax?
The YEARFRAC() function returns the fraction of a year represented by the number of whole days between two dates; the syntax is:
YEARFRAC(start_date, end_date{, basis])
What does the WEEKDAY() function do and what is the syntax?
The WEEKDAY() function returns the day of a week corresponding to a given value; and the syntax is:
WEEKDAY(serial_number{, return type])
What does the WORKDAY() function do and what is the syntax?
The WORKDAY() function returns a date that is a specified number of days before or after a given start date, exlcluding weekends and optionally holidays; and the syntax is:
WORKDAY(start_date, days{, holidays])
What does the NETWORKDAYS() function do and what is the syntax?
The NETWORKDAYS() function returns the number of workdays between two dates; the syntax is
NETWORKDAYS(start_date, end_date{, holidays])
What does the DATEDIF() function do and what is the syntax?
The DATEDIF() function calculates the number of days, months, or years between two dates; the syntax is:
DATEDIF(start_date, end_date, unit)
What does the MOD() function do and what is the syntax?
The MOD() function returns the remainder of a division; the syntax is:
MOD(number, divisor)
What is a spill range?
A spill range contains the resulta of a single dynamic array formula.
Only the first cell in the range is editable.
Cell formatting isn’t carried over from the source and doesn’t spill.
Spill range is highlighted with a blue border.
Spill range updates automatically and resize to fit the resulting array.
The # symbol can be used to reference an entire spilled range.
SPILL! errors
Spill errors occur when something is blocking the range; clearing the cell remedies it.
Spill errors occur when if you use a dynamic array formula inside of a table.
What does the SORT() function do and what is the syntax?
The SORT() function sorts an array of data by one or more columns in the array.
The syntas is =SORT(array[, sort_index][, sort_order][, by_col])
What does the SORTBY() function do and what is the syntax?
The SORTYBY() function sorts an array of data by one or more columns in another array.
The syntax is SORTBY(array, by_array[, sort_order][, array/order], […])
What does the FILTER() function do and what is the syntax?
The FILTER() function filters an array of data based specified criteria and returns the matching records.
The syntax is FILTER( array, include[, if_empty])
What does the UNIQUE() function do and what is the syntax?
The UNIQUE() function function removes duplicates from an array and returns only unique records.
The syntax is UNIQUE( array[, by_col][, exactly_once])
What does the SEQUENCE() function do and what is the syntax?
The SEQUENCE() function generates a one or two-dimensional array of sequential numbers.
The sytax is:
SEQUENCE(rows[,columns][, columns][, start][, step])
What does the RANDARRAY() function do and what is the syntax?
The RANDARRAY() function generates a one- or two-dimensional array of random numbers; the syntax is:
RANDARRAY({rows][, columns][, min][, max][, integer])
