Microsoft Excel - Advanced Excel Formulas & Functions

Question 1

What is the typical formula syntax in Excel?

Answer 1

First is the function name and then the arguments surrounded by parentheses.

Question 2

What are the two reference types in Excel?

Answer 2

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.

Question 3

What does the Evaluate Formula option do and how do you use it?

Answer 3

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.

Question 4

What do AND() statements accomplish and what is their syntax?

Answer 4

AND() statements evaluate various conditions and return TRUE only if all of the conditions are TRUE.

The syntax is AND(condition1, condition2, etc.).

Question 5

What do OR() conditions accomplish and what is their syntax?

Answer 5

OR() conditions evaluate various conditions and return TRUE if any one of them is TRUE.

Their syntax is OR(condition1, condition2, etc.)

Question 6

What do NOT() statements accomplish and what is their syntax?

Answer 6

NOT() statements negate the result of the condition being evaluated.

Their syntax can be either NOT(condition) or “<>”.

Question 7

What is the IFERROR() function for and what is the syntax?

Answer 7

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

Question 8

What are some of the most common IS statements?

Answer 8

ISBLANK
ISNUMBER
ISTEXT
ISERROR
ISEVEN
ISODD
ISLOGICAL
ISFORMULA

Question 9

What are some of the common statistical functions?

Answer 9

COUNT()
AVERAGE()
MEDIAN()
MODE()*
MAX()
MIN()
PERCENTILE()*
STDEV()*
VAR()*

*(expand)

Question 10

What does the function MODE() return and what is the syntax?

Answer 10

The MODE() function returns the most frequently ocurring or repetitive value in an array or range.

The syntax is MODE(number1, number2,…)

Question 11

What does the PERCENTILE() function return and what is the syntax?

Answer 11

The PERCENTILE() function returns the k-th percentile of values in a range.

The syntax is PERCENTILE(array, k).

Question 12

What does the STDEV() function return and what is the syntax?

Answer 12

The STDEV() function estimates standard deviation based on a sample.

The syntax is STDEV(number1, number2,…).

Question 13

What does the VAR() function return and what is the syntax?

Answer 13

The VAR() function estimates variance based on a sample.

The syntax is VAR(number1, number2,….).

Question 14

What do the SMALL() and LARGE() functions return and what is the syntax?

Answer 14

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

Question 15

What do the RAND() and RANDBETWEEN() functions return and what is the syntax?

Answer 15

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

Question 16

What does the SUMPRODUCT() function accomplish and what is the syntax?

Answer 16

The SUMPRODUCT() function returns the sum of products of corresponding ranges.

The syntax is SUMPRODUCT(array1, array2,…).

Question 17

What do COUNTIF(), SUMIF(), AND AVERAGEIF() functions return and what is the syntax?

Answer 17

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

Question 18

What do COUNTIFS(), SUMIFS(), AND AVERAGEIFS() functions return and what is the syntax?

Answer 18

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,…).

Question 19

How do you assign a name to a cell or range.

Answer 19

Select the cell or range and type in the name box the desired name:

Question 20

What do the ROW() and ROWS() function return and what is the syntax?

Answer 20

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

Question 21

What does the VLOOKUP () function do in Excel and what is the syntax?

Answer 21

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.

Question 22

What does the HLOOKUP () function do in Excel and what is the syntax?

Answer 22

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.

Question 23

What does the INDEX() function do and what is the syntax?

Answer 23

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

Question 24

What does the MATCH() function do and what is the syntax?

Answer 24

The MATCH() function returns the position of a given value within a one dimensional array; and the syntax is:

MATCH(value, array, [match_type])

Question 25

What is the XLOOKUP() function for and what is the syntax?

Answer 25

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

Question 26

What does the CHOOSE() function do and what is the syntax?

Answer 26

The CHOOSE() function selects a value from a list based on a given index number; the syntax is: CHOOSE(index, value\_1[, value\_2…]).

Question 27

What is the OFFSET() function and what is the syntax?

Answer 27

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

Question 28

What does the COUNTA() function do and what is the syntax?

Answer 28

The COUNTA() function gives the number of non-blank cells in a range and the syntax is: COUNTA(range)

Question 29

What does the TRIM() funtion do and what is the syntax?

Answer 29

The TRIM() funtion trims all leading and trailing spaces form a given string; the syntax is: TRIM(cell, value, or range)

Question 30

What does the UPPER() funtion do and what is the syntax? LOWER() PROPER()

Answer 30

The UPPER() funtion converts al characters to upper case of a given string; the syntax is: UPPER(cell, value, or range)

Question 31

What does the LOWER() funtion do and what is the syntax? PROPER()

Answer 31

The LOWER() funtion converts al characters to lower case of a given string; the syntax is: LOWER(cell, value, or range)

Question 32

What does the PROPER() funtion do and what is the syntax?

Answer 32

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)

Question 33

What does the CONCATENATE() or & function do and what is the syntax?

Answer 33

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.

Question 34

What is does the LEFT() function do and what is the syntax?

Answer 34

The LEFT() function return the specified number of leftmost characters in a string; and the syntaxt is: LEFT(text, [num\_chars])

Question 35

What is does the MID() function do and what is the syntax?

Answer 35

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

Question 36

What is does the RIGHT() function do and what is the syntax?

Answer 36

The RIGHT() function returns the specified number of rightmost characters from a string; and the syntaxt is: RIGHT(text, [num\_chars])

Question 37

What is does the LEN() function do and what is the syntax?

Answer 37

The LEN() function returns the length of a string; and the syntaxt is: LEN(text)

Question 38

What does the TEXT() function do and what is the syntax?

Answer 38

The TEXT() function converts a value into text and assigns a particular format; and the syntax is: TEXT(value, format\_text)

Question 39

What does the VALUE() function do and what is the syntax?

Answer 39

The VALUE() function converts a text into a numeric value; and the syntax is: VALUE(text)

Question 40

What does the SEARCH() function do and what is the syntax?

Answer 40

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

Question 41

What does the FIND() function do and what is the syntax?

Answer 41

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

Question 42

What does the SUBSTITUTE() function do and what is the syntax?

Answer 42

The SUBSTITUTE() function X; and the syntax is: SUBSTITUTE()

Question 43

What does the TODAY() function do and what is the syntax?

Answer 43

The TODAY() returns the current date; and the syntax is: TODAY()

Question 44

What does the NOW() function do and what is the syntax?

Answer 44

The NOW() returns the current date and time; and the syntax is: NOW()

Question 45

What does the YEAR() function do and what is the syntax?

Answer 45

The YEAR() function returns the year value of a given cell; and the syntax is: YEAR(cell)

Question 46

What does the MONTH() function do and what is the syntax?

Answer 46

The MONTH() function returns the month value of a given cell; and the syntax is: MONTH(cell)

Question 47

What does the DAY() function do and what is the syntax?

Answer 47

The DAY() function returns the day of a given cell; and the syntax is: DAY(cell)

Question 48

What does the HOUR() function do and what is the syntax?

Answer 48

The HOUR() function returns the hour of a given cell; and the syntax is: HOUR(cell)

Question 49

What does the MINUTE() function do and what is the syntax?

Answer 49

The MINUTE() function returns the minute of a given cell; and the syntax is: MINUTE(cell)

Question 50

What does the SECOND() function do and what is the syntax?

Answer 50

The SECOND() function returns the second of a given cell; and the syntax is: SECOND(cell)

Question 51

What does the EOMONTH() function do and what is the syntax?

Answer 51

The EOMONTH() function calculates the last day of given month; the syntax is: EOMONTH(start\_date, months)

Question 52

What does the YEARFRAC() function do and what is the syntax?

Answer 52

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

Question 53

What does the WEEKDAY() function do and what is the syntax?

Answer 53

The WEEKDAY() function returns the day of a week corresponding to a given value; and the syntax is: WEEKDAY(serial\_number{, return type])

Question 54

What does the WORKDAY() function do and what is the syntax?

Answer 54

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

Question 55

What does the NETWORKDAYS() function do and what is the syntax?

Answer 55

The NETWORKDAYS() function returns the number of workdays between two dates; the syntax is NETWORKDAYS(start\_date, end\_date{, holidays])

Question 56

What does the DATEDIF() function do and what is the syntax?

Answer 56

The DATEDIF() function calculates the number of days, months, or years between two dates; the syntax is: DATEDIF(start\_date, end\_date, unit)

Question 57

What does the MOD() function do and what is the syntax?

Answer 57

The MOD() function returns the remainder of a division; the syntax is: MOD(number, divisor)

Question 58

What is a spill range?

Answer 58

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.

Question 59

#SPILL! errors

Answer 59

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.

Question 60

What does the SORT() function do and what is the syntax?

Answer 60

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

Question 61

What does the SORTBY() function do and what is the syntax?

Answer 61

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], […])

Question 62

What does the FILTER() function do and what is the syntax?

Answer 62

The FILTER() function filters an array of data based specified criteria and returns the matching records. The syntax is FILTER( array, include[, if\_empty])

Question 63

What does the UNIQUE() function do and what is the syntax?

Answer 63

The UNIQUE() function function removes duplicates from an array and returns only unique records. The syntax is UNIQUE( array[, by\_col][, exactly\_once])

Question 64

What does the SEQUENCE() function do and what is the syntax?

Answer 64

The SEQUENCE() function generates a one or two-dimensional array of sequential numbers. The sytax is: SEQUENCE(rows[,columns][, columns][, start][, step])

Question 65

What does the RANDARRAY() function do and what is the syntax?

Answer 65

The RANDARRAY() function generates a one- or two-dimensional array of random numbers; the syntax is: RANDARRAY({rows][, columns][, min][, max][, integer])