Advanced Formulas and Functions - Excel

Categories

Lookup and Reference Functions

- VLOOKUP Function -

  • Searches for a value in the first column of a table and returns a value in the same row from a specified column.

  • Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • Example: =VLOOKUP(Apple, A2, 2, FALSE)

  • More information on the VLOOKUP Function: VLOOKUP Function Page

- HLOOKUP Function -

  • Searches for a value in the first row of a table and returns a value in the same column from a specified row.

  • Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

  • Example: =HLOOKUP(Q1, A1, 3, FALSE)

  • More information on the HLOOKUP Function: HLOOKUP Function Page

- INDEX Function -

  • Returns the value of an element in a table or array, selected by the row and column number indexes.

  • Syntax: =INDEX(array, row_num, [column_num])

  • Example: =INDEX(A1, 3, 2)

  • More information on the INDEX Function: INDEX Function Page

- MATCH Function -

  • Searches for a specified value in a range and returns the relative position of that value.

  • Syntax: =MATCH(lookup_value, lookup_array, [match_type])

  • Example: =MATCH(Banana, A1, 0)

  • More information on the MATCH Function: MATCH Function Page

- OFFSET Function -

  • Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

  • Syntax: =OFFSET(reference, rows, cols, [height], [width])

  • Example: =OFFSET(A1, 2, 3, 1, 1)

  • More information on the OFFSET Function: OFFSET Function Page

Text Functions

- CONCATENATE Function -

  • Joins two or more text strings into one string.

  • Syntax: =CONCATENATE(text1, text2, …) or =CONCAT(text1, text2, …)

  • Example: =CONCATENATE(Hello, , World!)

  • More information on the CONCATENATE Function: CONCATENATE Function Page

- TEXT Function -

  • Converts a value to text in a specified number format.

  • Syntax: =TEXT(value, format_text)

  • Example: =TEXT(1234.56, $#,##0.00)

  • More information on the TEXT Function: TEXT Function Page

- LEFT Function -

  • Returns the specified number of characters from the start of a text string.

  • Syntax: =LEFT(text, [num_chars])

  • Example: =LEFT(Hello, 2)

  • More information on the LEFT Function: LEFT Function Page

- RIGHT Function -

  • Returns the specified number of characters from the end of a text string.

  • Syntax: =RIGHT(text, [num_chars])

  • Example: =RIGHT(Hello, 2)

  • More information on the RIGHT Function: RIGHT Function Page

- MID Function -

  • Returns a specific number of characters from a text string, starting at the position you specify.

  • Syntax: =MID(text, start_num, num_chars)

  • Example: =MID(Hello, 2, 3)

  • More information on the MID Function: MID Function Page

- LEN Function -

  • Returns the number of characters in a text string.

    Syntax: =LEN(text)

    Example: =LEN(Hello)

  • More information on the LEN Function: LEN Function Page

- SEARCH Function -

  • Finds one text string within another and returns the number of the starting position of the first text string from the first character of the second text string.

  • Syntax: =SEARCH(find_text, within_text, [start_num])

  • Example: =SEARCH(l, Hello)

  • More information on the SEARCH Function: SEARCH Function Page

Logical Functions

- IF Function -

  • Checks whether a condition is met, and returns one value if TRUE and another value if FALSE.

  • Syntax: =IF(logical_test, value_if_true, value_if_false)

  • Example: =IF(A1 > 10, Yes, No)

  • More information on the IF Function: IF Function Page

- AND Function -

  • Returns TRUE if all arguments are TRUE.

  • Syntax: =AND(logical1, [logical2], …)

  • Example: =AND(A1 > 10, B1 < 5)

  • More information on the AND Function: AND Function Page

- OR Function -

  • Returns TRUE if any argument is TRUE.

  • Syntax: =OR(logical1, [logical2], …)

  • Example: =OR(A1 > 10, B1 < 5)

  • More information on the OR Function: OR Function Page

- NOT Function -

  • Reverses the logic of its argument.

  • Syntax: =NOT(logical)

  • Example: =NOT(A1 > 10)

  • More information on the NOT Function: NOT Function Page

- IFERROR Function -

  • Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

  • Syntax: =IFERROR(value, value_if_error)

  • Example: =IFERROR(A1/B1, Error)

  • More information on the IFERROR Function: IFERROR Function Page

- IFS Function -

  • Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

  • Syntax: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)

  • Example: =IFS(A1 > 10, Greater, A1 < 5, Smaller, TRUE, Equal)

  • More information on the IFS Function: IFS Function Page

Statistical and Mathematical Functions

- SUM Function -

  • Adds a range of cells.

  • Syntax: =SUM(number1, [number2], …)

  • Example: =SUM(A1)

  • More information on the SUM Function: SUM Function Page

- AVERAGE Function -

  • Returns the average (arithmetic mean) of the arguments.

  • Syntax: =AVERAGE(number1, [number2], …)

  • Example: =AVERAGE(A1)

  • More information on the AVERAGE Function: AVERAGE Function Page

- COUNT Function -

  • Counts the number of cells that contain numbers.

  • Syntax: =COUNT(value1, [value2], …)

  • Example: =COUNT(A1)

  • More information on the COUNT Function: COUNT Function Page

- MIN Function -

  • Returns the smallest number in a set of values.

  • Syntax: =MIN(number1, [number2], …)

  • Example: =MIN(A1)

  • More information on the MIN Function: MIN Function Page

- MAX Function -

  • Returns the largest number in a set of values.

  • Syntax: =MAX(number1, [number2], …)

  • Example: =MAX(A1)

  • More information on the MAX Function: MAX Function Page

- SUMIF Function -

  • Adds the cells specified by a given condition or criteria.

  • Syntax: =SUMIF(range, criteria, [sum_range])

  • Example: =SUMIF(A1, >5)

  • More information on the SUMIF Function: SUMIF Function Page

- AVERAGEIF Function -

  • Returns the average of all the cells in a range that meet a given criteria.

  • Syntax: =AVERAGEIF(range, criteria, [average_range])

  • Example: =AVERAGEIF(A1, >5)

  • More information on the AVERAGEIF Function: AVERAGEIF Function Page

Date and Time Functions

- TODAY Function -

  • Returns the current date.

  • Syntax: =TODAY()

  • Example: =TODAY()

  • More information on the TODAY Function: TODAY Function Page

- NOW Function -

  • Returns the current date and time.

  • Syntax: =NOW()

  • Example: =NOW()

  • More information on the NOW Function: NOW Function Page

- DATE Function -

  • Returns the serial number of a particular date.

  • Syntax: =DATE(year, month, day)

  • Example: =DATE(2023, 5, 31)

  • More information on the DATE Function: DATE Function Page

- DAY Function -

  • Converts a serial number to a day of the month.

  • Syntax: =DAY(serial_number)

  • Example: =DAY(A1)

  • More information on the DAY Function: DAY Function Page

- MONTH Function -

  • Converts a serial number to a month.

  • Syntax: =MONTH(serial_number)

  • Example: =MONTH(A1)

  • More information on the MONTH Function: MONTH Function Page

- YEAR Function -

  • Converts a serial number to a year.

  • Syntax: =YEAR(serial_number)

  • Example: =YEAR(A1)

  • More information on the YEAR Function: YEAR Function Page

- HOUR Function -

  • Converts a serial number to an hour.

  • Syntax: =HOUR(serial_number)

  • Example: =HOUR(A1)

  • More information on the HOUR Function: HOUR Function Page

- MINUTE Function -

  • Converts a serial number to a minute.

  • Syntax: =MINUTE(serial_number)

  • Example: =MINUTE(A1)

  • More information on the MINUTE Function: MINUTE Function Page

- SECOND Function -

  • Converts a serial number to a second.

  • Syntax: =SECOND(serial_number)

  • Example: =SECOND(A1)

  • More information on the SECOND Function: SECOND Function Page

Financial Functions

- PMT Function -

  • Calculates the payment for a loan based on constant payments and a constant interest rate.

  • Syntax: =PMT(rate, nper, pv, [fv], [type])

  • Example: =PMT(0.05/12, 60, -10000)

  • More information on the PMT Function: PMT Function Page

- FV Function -

  • Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

  • Syntax: =FV(rate, nper, pmt, [pv], [type])

  • Example: =FV(0.05/12, 60, -100, -1000)

  • More information on the FV Function: FV Function Page

- PV Function -

  • Returns the present value of an investment: the total amount that a series of future payments is worth now.

  • Syntax: =PV(rate, nper, pmt, [fv], [type])

  • Example: =PV(0.05/12, 60, -100)

  • More information on the PV Function: PV Function Page

- NPV Function -

  • Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.

  • Syntax: =NPV(rate, value1, [value2], …)

  • Example: =NPV(0.05, -10000, 3000, 4200, 6800)

  • More information on the NPV Function: NPV Function Page

- RATE Function -

  • Returns the interest rate per period of an annuity.

  • Syntax: =RATE(nper, pmt, pv, [fv], [type], [guess])

  • Example: =RATE(60, -100, 5000)

  • More information on the RATE Function: RATE Function Page

- IRR Function -

  • Returns the internal rate of return for a series of cash flows.

  • Syntax: =IRR(values, [guess])

  • Example: =IRR(A1)

  • More information on the IRR Function: IRR Function Page

Error Handling Functions

- IFERROR Function -

  • Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.

  • Syntax: =IFERROR(value, value_if_error)

  • Example: =IFERROR(A1/B1, Error)

  • More information on the IFERROR Function: IFERROR Function Page

- ISERROR Function -

  • Checks whether a value is an error and returns TRUE or FALSE.

  • Syntax: =ISERROR(value)

  • Example: =ISERROR(A1/B1)

  • More information on the ISERROR Function: ISERROR Function Page

- ERROR.TYPE Function -

  • Returns a number corresponding to an error type.

  • Syntax: =ERROR.TYPE(error_val)

  • Example: =ERROR.TYPE(A1)

  • More information on the ERROR.TYPE Function: ERROR.TYPE Function Page