Functions & Formulas List

 
Excel Logical Functions

Functions ☜ & ☞ Syntax 

IFS ⟹ =IFS (test1, value1, [test2, value2], ...)
IFERROR ⟹ =IFERROR (value, value_if_error)
IFNA ⟹ =IFNA(value, value_if_na)
NOT ⟹ =NOT (logical)
OR ⟹ =OR (logical1, [logical2], ...)
SWITCH ⟹ =SWITCH (expression, val1/result1, [val2/result2], ..., [default])


Excel Date and Time Functions

Functions ☜ & ☞ Syntax 

DATE ⟹ =DATE (year, month, day)
DATEDIF ⟹ =DATEDIF (start_date, end_date, unit)
DATEVALUE ⟹ =DATEVALUE (date_text)
DAY ⟹ =DAY (date)
DAYS ⟹ =DAYS (end_date, start_date)
DAYS360 ⟹ =DAYS360 (start_date, end_date, [method])
E DATE ⟹ =EDATE (start_date, months)
EOMONTH ⟹ =EOMONTH (start_date, months)
HOUR ⟹ =HOUR (serial_number)
MINUTE ⟹ =MINUTE (serial_number)
MONTH ⟹ =MONTH (date)
NETWORKDAYS ⟹ =NETWORKDAYS (start_date, end_date, [holidays])
NOW ⟹ =NOW ()
SECOND ⟹ =SECOND (serial_number)
TODAY ⟹ =TODAY ()
WEEKDAY ⟹ =WEEKDAY (serial_number, [return_type])
TIME ⟹ =TIME (hour, minute, second)
TIMEVALUE ⟹ =TIMEVALUE (time_text)



Excel Lookup and Reference Functions

Functions ☜ & ☞ Syntax 

ADDRESS ⟹ =ADDRESS (row_num, col_num, [abs_num], [a1], [sheet])
CHOOSE ⟹ =CHOOSE (index_num, value1, [value2], ...)
COLUMN ⟹ =COLUMN ([reference])
COLUMNS ⟹ =COLUMNS (array)
FORMULATEXT ⟹ =FORMULATEXT (reference)
GETPIVOTDATA ⟹ =GETPIVOTDATA (data_field, pivot_table, [field1, item1], ...)
HLOOKUP ⟹ =HLOOKUP (value, table, row_index, [range_lookup])
INDEX ⟹ =INDEX (array, row_num, [col_num], [area_num])
INDIRECT ⟹ =INDIRECT (ref_text, [a1])
LOOKUP ⟹ =LOOKUP (lookup_value, lookup_vector, [result_vector])
MATCH ⟹ =MATCH (lookup_value, lookup_array, [match_type])
OFFSET ⟹ =OFFSET (reference, rows, cols, [height], [width])
ROW ⟹ =ROW ([reference])
TRANSPOSE ⟹ =TRANSPOSE (array)
VLOOKUP ⟹ =VLOOKUP (value, table, col_index, [range_lookup])


Excel Math Functions

Functions ☜ & ☞ Syntax 

SUM ⟹ =SUM (number1, [number2], [number3]……)
SUMIF⟹ =SUMIF (range, criteria, [sum_range])

SUMIFS ⟹ =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

SUMPRODUCT ⟹ =SUMPRODUCT (array1,[array2],[array3],…)
SUBTOTAL ⟹ =(method, range1, [range2 …range_n])
ROUND ⟹ =ROUND(number,num_digits)
ROUNDUP ⟹ =ROUNDUP(number,num_digits)
ROUNDDOWN ⟹ =ROUNDDOWN(number,num_digits)
RAND ⟹ =RAND ()
RANDBETWEEN ⟹ =RANDBETWEEN(bottom,top)
PRODUCT ⟹ =PRODUCT (number1, [number2], ...)
POWER ⟹ =POWER (number, power)
MOD ⟹ =MOD(number,divisor)



Excel Statistical Functions

Functions ☜ & ☞ Syntax 

COUNT ⟹ =COUNT (value1, [value2], ...)
COUNTA ⟹ =COUNTA (value1, [value2], ...)
COUNTIF ⟹ =COUNTIF (range, criteria)
COUNTIFS ⟹ =COUNTIFS (range1, criteria1, [range2], [criteria2], ...)
COUNTBLANK ⟹ =COUNTBLANK (range)

LARG ⟹ =LARGE (array, n)
MAX ⟹ =MAX (number1, [number2], ...)
MAXA ⟹ =MAXA (value1, [value2], ...)
MAXIFS ⟹ =MAXIFS (max_range, range1, criteria1, [range2], [criteria2], ...)

MEDIAN ⟹ =MEDIAN (number1, [number2], ...)
MIN ⟹ =MIN (number1, [number2], ...)
MINA ⟹ =MINA (value1, [value2], ...)
MINIFS ⟹ =MINIFS (min_range, range1, criteria1, [range2], [criteria2], ...)

FREQUENCY ⟹ =FREQUENCY (data_array, bins_array)
AVERAGE ⟹ =AVERAGE (number1, [number2], ...)
AVERAGEA ⟹ =AVERAGEA (value1, [value2], ...)
AVERAGEIF ⟹ =AVERAGEIF (range, criteria, [average_range])
AVERAGEIFS ⟹ =AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)

SMALL ⟹ =SMALL (array, n)


Dynamic array

Functions ☜ & ☞ Syntax 

FILTER ⟹ =FILTER (array, include, [if_empty])
UNIQUE ⟹ =UNIQUE (array, [by_col], [exactly_once])

XLOOKUP ⟹ =XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
XMATCH ⟹ =XMATCH (lookup_value, lookup_array, [match_mode], [search_mode])
LET ⟹ =LET (name1, value1, [name2/value2], ..., calculation)
SORTBY ⟹ =SORTBY (array, by_array, [sort_order], [array/order], ...)
RANDARRAY ⟹ =RANDARRAY ([rows], [columns], [min], [max], [integer])
SEQUENCE ⟹ =SEQUENCE (rows, [columns], [start], [step])
ARRAYTOTEXT ⟹ =ARRAYTOTEXT(array, [format])
BYCOL ⟹ = BYCOL(array, [function])
BYROW ⟹ = BYROW(array, [function])
CHOOSECOLS ⟹ =CHOOSECOLS(array, col_num1, [col_num2], ...)
CHOOSEROWS ⟹ = CHOOSEROWS(array, row_num1, [row_num2], ...)
DROP ⟹ = DROP(array, [rows], [col])
EXPAND ⟹ = EXPAND(array, [rows], [columns], [pad_with])
HSTACK ⟹ = HSTACK(array1, [array2], ...)
ISOMITTED ⟹ = ISOMITTED(argument)
LAMBDA ⟹ = LAMBDA(parameter, or, calculation)
MAKEARRAY ⟹ = MAKEARRAY(rows, columns, function)
MAP ⟹ = MAP(array1, [array2], ..., lambda)
REDUCE ⟹ = REDUCE([initial_value], array, function)
SCAN ⟹ = SCAN([initial_value], array, function)
STOCK HISTORY ⟹ = STOCK HISTORY (stock, start_date, [end_date], [interval], [headers], [properties], ...)
TAKE ⟹ = TAKE(array, [rows], [col])
TEXTAFTER ⟹ = TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
TEXTBEFORE ⟹ = TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
TEXTSPLIT ⟹ = TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
TOCOL ⟹ = TOCOL(array, ignore, scan_by_column)
TOROW ⟹ = TOROW(array, ignore, scan_by_column)
VALUETOTEXT ⟹ = VALUETOTEXT(value, [format])
VSTACK ⟹ = VSTACK(array1, [array2], ...)
WRAPCOLS ⟹ = WRAPCOLS(vector, wrap_count, [pad_with])
WRAPROWS ⟹ = WRAPROWS(vector, wrap_count, [pad_with])


Excel Text Functions

Functions ☜ & ☞ Syntax 

CONCATENATE ⟹ =CONCATENATE (text1, text2, [text3], ...)
LEFT ⟹ =LEFT (text, [num_chars])
LEN ⟹ =LEN (text)
LOWER ⟹ =LOWER (text)
MID ⟹ =MID (text, start_num, num_chars)
SEARCH ⟹ =SEARCH (find_text, within_text, [start_num])
SUBSTITUTE ⟹ =SUBSTITUTE (text, old_text, new_text, [instance])
TEXT ⟹ =TEXT (value, format_text)
TRIM ⟹ =TRIM (text)
REPLACE ⟹ =REPLACE (old_text, start_num, num_chars, new_text)
RIGHT ⟹ =RIGHT (text, [num_chars])
UPPER ⟹ =UPPER (text)
VALUE ⟹ =VALUE (text)
FIND ⟹ =FIND (find_text, within_text, [start_num])
FIXED ⟹ =FIXED (number, [decimals], [no_commas])


Excel Financial Functions

Functions ☜ & ☞ Syntax 

ACCRINT ⟹ 
=ACCRINT(id, fd, sd, rate, par, freq, [basis], [calc])
ACCRINTM ⟹ =ACCRINTM(id, sd, rate, par, [basis])
AMORDEGRC ⟹ =AMORDEGRC(cost, purchase, first, salvage, period, rate, [basis])
AMORLINC ⟹ =AMORLINC(cost, purchase, first, salvage, period, rate, [basis])
COUPDAYBS ⟹ =COUPDAYBS(settlement, maturity, frequency, [basis])
COUPDAYS ⟹ =COUPDAYS(settlement, maturity, frequency, [basis])
COUPDAYSNC ⟹ =COUPDAYSNC(settlement, maturity, frequency, [basis])
COUPNCD ⟹ =COUPNCD(settlement, maturity, frequency, [basis])
COUPNUM ⟹ =COUPNUM(settlement, maturity, frequency, [basis])
COUPPCD ⟹ =COUPPCD(settlement, maturity, frequency, [basis])
CUMIPMT ⟹ =CUMIPMT(rate, nper, pv, start_period, end_period, type)
CUMPRINC ⟹ =CUMPRINC(rate, nper, pv, start_period, end_period, type)
DB ⟹ =DB(cost, salvage, life, period, [month])
DDB ⟹ =DDB(cost, salvage, life, period, [factor])
DISC ⟹ =DISC(settlement, maturity, pr, redemption, [basis])
DOLLARDE ⟹ =DOLLARDE(fractional_dollar, fraction)
DOLLARFR ⟹ =DOLLARFR(decimal_dollar, fraction)
DURATION ⟹ =DURATION(settlement, maturity, coupon, yld, freq, [basis])
EFFECT ⟹ =EFFECT(nominal_rate, npery)
FV ⟹ =FV(rate, nper, pmt, [pv], [type])
FVSCHEDULE ⟹ =FVSCHEDULE(principal, schedule)
INTRATE ⟹ =INTRATE(settlement, maturity, investment, redemption, [basis])
IPMT ⟹ =IPMT(rate, per, nper, pv, [fv], [type])
IRR ⟹ =IRR(values, [guess])
ISPMT ⟹ =ISPMT(rate, per, nper, pv)
MDURATION ⟹ =MDURATION(settlement, maturity, coupon, yld, freq, [basis])
MIRR ⟹ =MIRR(values, finance_rate, reinvest_rate)
NOMINAL ⟹ =NOMINAL(effect_rate, npery)
NPER ⟹ =NPER(rate, pmt, pv, [fv], [type])
NPV ⟹ =NPV(rate, value1, [value2], ...)
ODDFPRICE ⟹ =ODDFPRICE(sd, md, id, fd, rate, yld, redem, freq, [basis])
ODDFYIELD ⟹ =ODDFYIELD(sd, md, id, fd, rate, pr, redem, freq, [basis])
ODDLPRICE ⟹ =ODDLPRICE(sd, md, id, rate, yld, redem, freq, [basis])
ODDLYIELD ⟹ =ODDLYIELD(sd, md, ld, rate, pr, redem, freq, [basis])
PDURATION ⟹ =PDURATION(rate, pv, fv)
PMT ⟹ =PMT(rate, nper, pv, [fv], [type])
PPMT ⟹ =PPMT(rate, per, nper, pv, [fv], [type])
PRICE ⟹ =PRICE(sd, md, rate, yld, redemption, frequency, [basis])
PRICEDISC ⟹ =PRICEDISC(sd, md, discount, redemption, [basis])
PRICEMAT ⟹ =PRICEMAT(sd, md, id, rate, yld, [basis])
PV ⟹ =PV(rate, nper, pmt, [fv], [type])
RATE ⟹ =RATE(nper, pmt, pv, [fv], [type], [guess])
RECEIVED ⟹ =RECEIVED(settlement, maturity, investment, discount, [basis])
RRI ⟹ =RRI(nper, pv, fv)
SLN ⟹ =SLN(cost, salvage, life)
SYD ⟹ =SYD(cost, salvage, life, period)
TBILLEQ ⟹ =TBILLEQ(settlement, maturity, discount)
TBILLPRICE ⟹ =TBILLPRICE(settlement, maturity, discount)
TBILLYIELD ⟹ =TBILLYIELD(settlement, maturity, price)
VDB ⟹ =VDB(cost, salvage, life, start, end, [factor], [no_switch])
XIRR ⟹ =XIRR(values, dates, [guess])
XNPV ⟹ =XNPV(rate, values, dates)
YIELD ⟹ =YIELD(sd, md, rate, pr, redemption, frequency, [basis])
YIELDDISC ⟹ =YIELDDISC(sd, md, pr, redemption, [basis])
YIELDMAT ⟹ =YIELDMAT(sd, md, id, rate, pr, [basis])


Excel Information Functions

Functions ☜ & ☞ Syntax 

CELL ⟹ = CELL(info_type, [reference])
ISBLANK ⟹ = ISBLANK(value)
ISERR ⟹ = ISERR(value)
ISERROR ⟹ = ISERROR(value)
ISNA ⟹ = ISNA(value)
ISTEXT ⟹ =
ISTEXT (value)
ISFORMULA ⟹ =ISFORMULA(reference)
ISLOGICAL ⟹ =ISLOGICAL(value)
ISEVEN ⟹ = ISEVEN(number)



Sources of Information






تعليقات

المشاركات الشائعة من هذه المدونة

دوال الجمع مع الأمثلة (SUM, SUMIF, SUMIFS)

دوال العد مع الأمثلة (Count,Countif,Countifs)

دوال حساب المتوسط (AVERAGE, AVERAGEA, AVERAGEIF & AVERAGEIFS)

contact us

الاسم

بريد إلكتروني *

رسالة *