Excel Functions - Powerful tools to aid calculations

Excel Functions are extensively used in calculations under different situations and help various fields. They can be broadly classified under the following categories:  Financial, Math/Trigonometry,  Statistical.  Date/Time, Information, Logical, Lookup/Reference, Text, Engineering, and Database functions.

Excel functions contain defined parameters to suit calculation requirements under various categories. Most of them are preset formulas based on requirements expected in different fields.   They can be simple in nature just to help to do quick calculations like sum, average, and count, for a range of cells. There are also complicated advanced functions like the IRR.

The Excel functions have a standard mechanism in which the requirements must be defined in order to derive the required return. So, understanding the way the various required parameters must be fed into the function formula is very essential to get the correct output. The way the function is written is called the syntax. If we want the result of a function to be returned in a cell, we should start by typing =, then the function name (say, AVERAGE), followed by the Arguments within parenthesis. Then by typing enter, we allow the function to perform the calculation as pre-defined and return the result.

For example,

=Average (C1:C20) helps calculate the average of the numbers in the range C1 to C20.

Arguments can refer to both individual cells and cell ranges and we can include one argument or multiple arguments, depending on the syntax required for the function.

The knowledge about how to enter the arguments is very important because Excel will not always warn us if wrong results are returned when we have entered the parameters which are undesirable.

Our section under functions explains all the major functions in MS Excel in simple language.

Financial Functions

COUPNCD Get next coupon date after settlement date settlementmaturityfrequencybasis
YIELDDISC Get annual yield for discounted security settlementmaturityprredemptionbasis
YIELD Get yield for security that pays periodic interest settlementmaturityrateprredemptionfrequencybasis
XNPV Calculate net present value for irregular cash flows ratevaluesdates
TBILLYIELD Get yield for a Treasury bill settlementmaturitypr
TBILLPRICE Get price per $100 Treasury bill settlementmaturitydiscount
TBILLEQ Get bond-equivalent yield for a Treasury bill settlementmaturitydiscount
RRI Get equivalent interest rate for growth nperpvfv
RECEIVED Get amount received at maturity settlementmaturityinvestmentdiscountbasis
PRICEMAT Get price per $100 interest at maturity settlementmaturityissuerateyldbasis
PRICEDISC Get price per $100 discounted security settlementmaturitydiscountredemptionbasis
PRICE Get price per $100 face value - periodic interest settlementmaturityrateyldredemptionfrequencybasis
PDURATION Get periods required to reach given value ratepvfv
ODDLYIELD Get yield of security with odd last period settlementmaturityissuefirst_couponrateprredemptionfrequencybasis
ODDLPRICE Get price per $100 face value with odd last period settlementmaturitylast_interestrateyldredemptionfrequencybasis
ODDFPRICE Get price per $100 odd first period settlementmaturityissuefirst_couponrateyldredemptionfrequencybasis
NOMINAL Get annual nominal interest rate effect_ratenpery
MDURATION Get Macauley modified duration par value of $100 settlementmaturitycouponyldfrequencybasis
INTRATE Get interest rate for fully invested security settlementmaturityinvestmentredemptionbasis
FVSCHEDULE Get future value of principal compound interest principalschedule
EFFECT Get effective annual interest rate nominal_ratenpery
DURATION Get annual duration with periodic interest settlementmaturitycouponyldfrequencybasis
DOLLARDE Convert dollar price as fraction to decimal fractional_dollarfraction
DISC Get discount rate for a security settlementmaturityprredemptionbasis
COUPPCD Get previous coupon date before settlement date settlementmaturityfrequencybasis
COUPNUM Get number of coupons payable settlementmaturityfrequencybasis
COUPDAYSNC Get days from settlement date to next coupon date settlementmaturityfrequencybasis
COUPDAYBS Get days from coupon period to settlement date settlementmaturityfrequencybasis
XIRR Calculate internal rate of return for irregular cash flows valuesdatesguess
VDB Depreciation - double-declining variable costsalvagelifestart_periodend_periodfactorno_switch
SYD Depreciation - sum-of-years costsalvagelifeper
SLN Depreciation - straight-line costsalvagelife
NPV Calculate net present value ratevalue1value2
NPER Get number of periods for loan or investment ratepmtpvfvtype
MIRR Calculate modified internal rate of return valuesfinance_ratereinvest_rate
ISPMT Get interest paid for specific period ratepernperpv
DDB Depreciation - double-declining costsalvagelifeperiodfactor
DB Depreciation - fixed-declining balance costsalvagelifeperiodmonth
AMORLINC Depreciation for accounting period costdate_purchasedfirst_periodsalvageperiodratebasis
AMORDEGRC Depreciation for accounting period coefficient costdate_purchasedfirst_periodsalvageperiodratebasis
ACCRINT Get accrued interest periodic issuefirst_interestsettlementrateparfrequencybasiscalc_method
FV Get the future value of an investment ratenperpmtpvtype
IPMT Get interest in given period ratepernperpvfvtype
IRR Calculate internal rate of return valuesguess
PMT Get the periodic payment for a loan ratenperpvfvtype
PPMT Get principal payment in given period ratepernperpvfvtype
PV Get the present value of an investment ratenperpmtfvtype
RATE Get the interest rate per period of an annuity nperpmtpvfvtypeguess
ACCRINTM Get accrued interest at maturity issuesettlementrateparbasis
ODDFYIELD Get yield security with odd first period settlementmaturityissuefirst_couponrateprredemptionfrequencybasis
DOLLARFR Convert price to fractional notation decimal_dollarfraction
CUMPRINC Get cumulative principal paid on a loan ratenperpvstart_periodend_periodtype

Statistical Functions

AVEDEV Get sum of squared deviations number1number2...
AVERAGE Get the average of a group of numbers number1number2...
AVERAGEA Get the average of a group of numbers and text value1value2...
AVERAGEIF Get the average of numbers that meet criteria rangecriteriaaverage_range
AVERAGEIFS Average cells that match multiple criteria average_rangecriteria_range1criteria1range2criteria2
BETA.DIST Returns the beta probability distrubution function xalphabetacumulativeAB
BETA.INV Returns the inverse of the beta cumulative probability density function probabilityalphabetaAB
BINOM.DIST Rerturns the individual term binomial distrubution probabilty number_strialsprobability_scumulative
COUNT Count numbers value1value2...
COUNTA Count the number of non-blank cells value1value2...
COUNTBLANK Count cells that are blank range
COUNTIF Count cells that match criteria rangecriteria
COUNTIFS Count cells that match multiple criteria criteria_rangecriteria1range2criteria2
FORECAST Calculates future value xknown_ysknown_xs
FREQUENCY Get the frequency of values in a data set data_arraybins_array
INTERCEPT Calculates the point at which a line will intersect the Y axis known_ysknown_xs
LARGE Get the nth largest value arrayk
MAX Get the largest value number1number2...
MAXA Return largest value. value1value2...
MEDIAN Get the median of a group of numbers number1number2...
MIN Get the smallest value. number1number2...
MINA Return smallest value. value1value2...
MODE.MULT Get most frequently occurring numbers number1number2...
MODE.SNGL Get most frequently occurring number number1number2...
PERMUT Get total number of permutations numbernumber_chosen
SLOPE Returns the slope of linear regression line known_ysknown_xs
SMALL Get the nth smallest value arrayk
STDEVA Get standard deviation in a sample value1value2...
VARA Get variation of a sample value1value2...
VARPA Get variation of a population value1value2...
MINIFS Get minimum value with criteria min_rangecriteria_range1criteria1range2criteria2
VAR.S Get variation of a sample number1number2...
VAR.P Get variation of population number1number2...
TRIMMEAN Calculate mean excluding outliers arraypercent
STDEV.S Get the standard deviation in a sample number1number2...
STDEV.P Get standard deviation of population number1number2...
RANK.EQ Rank a number against a range of numbers numberreforder
RANK.AVG Rank a number against a range of numbers numberreforder
QUARTILE.INC Get the quartile in a data set arrayquart
QUARTILE.EXC Get the quartile in a data set arrayquart
PERCENTRANK.INC Get percentile rank, inclusive arrayxsignificance
PERCENTRANK.EXC Get percentile rank, exclusive arrayxsignificance
PERCENTILE.INC Get kth percentile arrayk
HARMEAN Calculate harmonic mean number1number2...
MAXIFS Get maximum value with criteria max_rangecriteria_rangecriteriarange2criteria2
GEOMEAN Calculate geometric mean number1number2...
DEVSQ Get sum of squared deviations number1number2...
STDEVPA Get standard deviation for a population value1value2...
PERCENTRANK Get percentile rank, inclusive arrayxsignificance
QUARTILE Get the quartile in a data set arrayquart
MODE Get most frequently occurring number number1number2...
COVAR Returns covaraiance of the product array1array2
BINOMDIST Returns the indiviadual term binomial distribution probabilty number_strialsprobability_scumulative
STDEV Get the standard deviation in a sample number1number2...
VARP Get variation of a population number1number2...
RANK Rank a number against a range of numbers numberreforder
VAR Get variation of a sample number1number2...

Logical Functions

Lookup & Reference Functions

FORMULATEXT Get the formula in a cell reference
UNIQUE Returns the unique value from a range or array arrayby_colexactly_once
SORTBY Sorts a range or array based on there corresponding values arrayby_arraysort_order
SORT Sorts a range or array arraysort_indexsort_orderby_col
ADDRESS Create a cell address from a given row and column row_numcolumn_numabs_numa1sheet_text
AREAS Get the number of areas in a reference. reference
CHOOSE Get a value from a list based on position index_numvalue1value2
COLUMN Get the column number of a reference. reference
COLUMNS Get the number of columns in an array or reference. array
HLOOKUP Look up a value in a table by matching on the first row lookup_valuetable_arrayrow_index_numrange_lookup
HYPERLINK Create a clickable link. link_locationfriendly_name
INDEX Get a value in a list or table based on location referencerow_numcolumn_numarea_num
INDIRECT Create a reference from text ref_texta1
MATCH Get the position of an item in an array lookup_valuelookup_arraymatch_type
OFFSET Create a reference offset from given starting point referencerowscolsheightwidth
ROW Get the row number of a reference reference
ROWS Get the number of rows in an array or reference. array
VLOOKUP Lookup a value in a table by matching on the first column lookup_valuetable_arraycol_index_numrange_lookup
LOOKUP Look up a value in a one-column range lookup_valuelookup_vectorresult_vector
TRANSPOSE Flip the orientation of a range of cells array
GETPIVOTDATA Retrieve data from a pivot table in a formula data_fieldpivot_tablefield1item1...
FILTER Filter a range or array arrayincludeif_empty

Text Functions

BAHTTEXT Converts a number to text number
CHAR Get a character from a number number
CLEAN Strip non-printable characters from text text
CODE Get the code for a character text
CONCAT Join text values without delimiter text1...
CONCATENATE Join text together text1text2...
DOLLAR Convert a number to text in currency format numberdecimals
EXACT Compare two text strings text1text2
FIND Get the location of text in a string find_textwithin_textstart_num
FIXED Format number as text with fixed decimals numberdecimalsno_commas
LEFT Extract text from the left of a string textnum_chars
LEN Get the length of text. text
LOWER Convert text to lower case text
MID Extract text from inside a string textstart_numnum_chars
NUMBERVALUE Convert text to number with custom separators textdecimal_separatorgroup_separator
PROPER Capitalize the first letter in each word text
REPLACE Replace text based on location old_textstart_numnum_charsnew_text
REPT Repeat text as specified textnumber_times
RIGHT Extract text from the right of a string textnum_chars
SEARCH Get the location of text in a string find_textwithin_textstart_num
SUBSTITUTE Replace text based on content textold_textnew_textinstance_num
T Checks whether a value is text value
TEXT Convert a number to text in a number format valueformat_text
TEXTJOIN Join text values with a delimiter delimiterignore_emptytext1text2…
TRIM Remove extra spaces from text text
UNICHAR Get Unicode character by number number
UNICODE Get number from Unicode character text
UPPER Convert text to upper case text
VALUE Convert text to a number text

Date & Time Functions

TIMEVALUE Get a valid time from a text string time_text
YEARFRAC Get the fraction of a year between two dates start_dateend_datebasis
DATE Create a date with year, month, and day yearmonthday
DATEDIF Get days, months, or years between two dates start_dateend_dateunit
DATEVALUE Convert a date in text format to a valid date date_text
DAY Get the day as a number (1-31) from a date serial_number
DAYS Get days between dates end_datestart_date
DAYS360 Get days between 2 dates in a 360-day year start_dateend_datemethod
EDATE Shift date n months in future or past start_datemonths
EOMONTH Get last day of month n months in future or past start_datemonths
HOUR Get the hour as a number (0-23) from a Time serial_number
ISOWEEKNUM Get ISO week number for a given date date
MINUTE Get minute as a number (0-59) from time serial_number
MONTH Get month as a number (1-12) from a date serial_number
NETWORKDAYS Get the number of working days between two dates start_dateend_dateholidays
NETWORKDAYS.INTL Get work days between two dates start_dateend_dateweekendholidays
NOW Get the current date and time
SECOND Get the Second as a number (0-59) from a Time serial_number
TIME Create a time with hours, minutes, and seconds hourminutesecond
TODAY Get the current date
WEEKDAY Get the day of the week as a number serial_numberreturn_type
WEEKNUM Get the week number for a given date serial_numberreturn_type
WORKDAY Get a date n working days in the future or past start_datedaysholidays
WORKDAY.INTL Get date n working days in future or past start_datedaysweekendholidays
YEAR Get the year from a date serial_number

Math & Trig Functions

CEILING Round a number up to nearest multiple numbersignificance
FLOOR Round a number down to the nearest specified multiple numbersignificance
ACOS Returns the arccosine of the number number
ACOSH Returns the inverse hyperbolic cosine of the number number
ASIN Get the angle of a triangle. number
ASINH Returns the inverse hyperbolic sine of the number number
ATAN Returns the arctangent of a number in radians number
ATAN2 Returns the arctangent of the specified X-and Y-coordinates x_numy_num
ATANH Returns the inverse hyperbolic tangent of a number number
CEILING.PRECISE Round a number up to nearest multiple number[significance]
COMBINA Returns the number of combinations with repetitions for a given number of itmes numbernumber_chosen
COS Get the cosine of an angle number
COSH Returns the hyperbolic cosine of a number number
DEGREES Converts radians to degrees angle
EVEN Round a number up to the next even integer number
FACT Find the factorial of a number number
INT Get the integer part of a decimal by rounding down number
LN Get the natural logarithm of a number number
LOG10 Get the base-10 logarithm of a number number
MDETERM Get matrix determinant of given array array
MINVERSE Get inverse matrix of array array
MMULT Returns the mattrix product of two arrays array1array2
ODD Round a number up to the next odd integer number
PI Get the value of ?
POWER Raise a number to a power numberpower
PRODUCT Get the product of supplied numbers number1number2
RADIANS Converts degrees into radians angle
RAND Get a random number between 0 and 1
RANDBETWEEN Get a random integer between two values bottomtop
ROMAN Converts numbers to Roman numerals numberform
ROUND Round a number to a given number of digits numbernum_digits
ROUNDDOWN Round down to given number of digits numbernum_digits
ROUNDUP Round a number up to a given number of digits numbernum_digits
SIGN Get the sign of a number number
SIN Get the sine of an angle number
SINH Returns the hyperbolic sine of a number number
SQRT Find the positive square root of a number number
SUBTOTAL Get a subtotal in a list or database function_numref1
SUM Add numbers together number1number2
SUMIF Sum numbers in a range that meet supplied criteria rangecriteria
SUMIFS Sum cells that match multiple criteria sum_rangecriteria_range
SUMSQ Returns the sum of squares of the argument number1number2
STDEVP Calculates the standard deviation number1number2
SUMX2PY2 Returns the total of the sums of square of numbers in two corresponding ranges array_xarray_y
SUMXMY2 Sums the square of the differences in two corresponding ranges array_xarray_y
TAN Get the tangent of an angle number
ABS Find the absolute value of a number number
COMBIN Returns the number of combinations numbernumber_chosen
LOG Get the logarithm of a number numberbase
RANDARRAY Returns an array of numbers rowscolumns
SEQUENCE Returns a sequence of numbers rowscolumns
MROUND Round a number to the nearest specified multiple numbermultiple
LCM Get the least common multiple or two or more numbers number1number2
GCD Get the greatest common divisor of two or more numbers number1number2
FLOOR.MATH Round number down to nearest multiple numbersignificance
FACTDOUBLE Get double factorial of a number number
DECIMAL Converts an alpha-numeric number to decimal numberradix
CEILING.MATH Round a number up to nearest multiple numbersignificance
ARABIC Converts a Roman numerals to an Arabic numerals text
TRUNC Truncate a number to a given precision numbernum_digits
SUMPRODUCT Multiply, then sum arrays array1array2
MOD Get the remainder from division numberdivisor
EXP Find the value of e raised to the power of a number number
AGGREGATE Return aggregate calculation function_numoptions

Database Functions

Information Functions