TOP FUNCTIONS FOR DATA ANALYSIS AND FINANCIAL MODELLING

Here is a comprehensive list of advanced Excel formulas and functions, categorized for convenience:

---

Lookup and Reference

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

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

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

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

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

XLOOKUP (Excel 365/2021): =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

CHOOSE: =CHOOSE(index_num, value1, [value2], ...)

---

Logical

IF: =IF(logical_test, value_if_true, value_if_false)

IFS: =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

AND: =AND(logical1, [logical2], ...)

OR: =OR(logical1, [logical2], ...)

NOT: =NOT(logical)

---

Text

CONCATENATE (or CONCAT in newer versions): =CONCATENATE(text1, [text2], ...)

TEXT: =TEXT(value, format_text)

TRIM: =TRIM(text)

LEN: =LEN(text)

LEFT: =LEFT(text, num_chars)

RIGHT: =RIGHT(text, num_chars)

MID: =MID(text, start_num, num_chars)

FIND: =FIND(find_text, within_text, [start_num])

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

REPLACE: =REPLACE(old_text, start_num, num_chars, new_text)

SUBSTITUTE: =SUBSTITUTE(text, old_text, new_text, [instance_num])

TEXTJOIN (Excel 365/2021): =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

---

Math and Statistical

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

SUMIFS: =SUMIFS(sum_range, criteria_range1, criteria1, ...)

COUNTIF: =COUNTIF(range, criteria)

COUNTIFS: =COUNTIFS(criteria_range1, criteria1, ...)

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

AVERAGEIFS: =AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

ROUND: =ROUND(number, num_digits)

ROUNDUP: =ROUNDUP(number, num_digits)

ROUNDDOWN: =ROUNDDOWN(number, num_digits)

RAND: =RAND()

RANDBETWEEN: =RANDBETWEEN(bottom, top)

LARGE: =LARGE(array, k)

SMALL: =SMALL(array, k)

RANK: =RANK(number, ref, [order])

MEDIAN: =MEDIAN(number1, [number2], ...)

MODE: =MODE(number1, [number2], ...)

PRODUCT: =PRODUCT(number1, [number2], ...)

---

Date and Time

TODAY: =TODAY()

NOW: =NOW()

DATE: =DATE(year, month, day)

YEAR: =YEAR(serial_number)

MONTH: =MONTH(serial_number)

DAY: =DAY(serial_number)

HOUR: =HOUR(serial_number)

MINUTE: =MINUTE(serial_number)

SECOND: =SECOND(serial_number)

WEEKDAY: =WEEKDAY(serial_number, [return_type])

WORKDAY: =WORKDAY(start_date, days, [holidays])

NETWORKDAYS: =NETWORKDAYS(start_date, end_date, [holidays])

---

Financial

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

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

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

NPV: =NPV(rate, value1, [value2], ...)

IRR: =IRR(values, [guess])

XNPV: =XNPV(rate, values, dates)

XIRR: =XIRR(values, dates, [guess])

---

Array and Dynamic Arrays (Excel 365/2021)

UNIQUE: =UNIQUE(array, [by_col], [exactly_once])

FILTER: =FILTER(array, include, [if_empty])

SORT: =SORT(array, [sort_index], [sort_order], [by_col])

SEQUENCE: =SEQUENCE(rows, [columns], [start], [step])

RANDARRAY: =RANDARRAY([rows], [columns], [min], [max], [integer])

---

Error Handling

IFERROR: =IFERROR(value, value_if_error)

IFNA: =IFNA(value, value_if_na)

ERROR.TYPE: =ERROR.TYPE(error_val)

---

Thank you for visiting our site you can also Mail me.