**Excel Formulas PDF and Functions:**

What do you Understand by the **Advanced Excel Formulas and **Basic Excel Functions?

This Blog will give you the **Excel formulas PDF**/list of the Key Functions of Excel. Excel Formulas PDF is a list of most useful or extensively used excel formulas in day to day working life with Excel. These formulas, we can use in Excel 2013. 2016 as well as 2019.

- Compatibility Excel Formulas & Functions
- Cube Excel Formulas & Functions
- Database Excel Formulas & Functions
- Date & Time Excel Formulas & Functions
- Information Excel Formulas & Functions
- Logical Excel Formulas & Functions
- Lookup & Reference Excel Formulas & Functions
- Text Excel Formulas & Functions
- Most Common Excel Formulas & Functions
- Engineering Excel Formulas & Functions
- Financial Excel Formulas & Functions
- Math & Trigonometry Excel Formulas & Functions
- Statistical Excel Formulas & Functions

**Must Check:** 249+ Excel Shortcuts PDF to Work Faster

## Excel Formulas PDF List

### Compatibility Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

CONCATENATE | =CONCATENATE(text1,text2,…) | Joins several text items into one text item. Easier to use ‘&’ instead of the function usually. |

FLOOR | =FLOOR(number,significance) | Rounds a number down, toward zero |

BINOMDIST | =BINOMDIST(number_s,trials,probability_s,cumulative) | Returns the individual term binomial distribution probability |

CHIDIST | =CHIDIST(x,deg_freedom) | Returns the one-tailed probability of the chi-squared distribution |

CHIINV | =CHITEST(actual_range,expected_range) | Returns the test for independence |

CONFIDENCE | =CONFIDENCE(alpha,standard_dev,size) | Returns the confidence interval for a population mean |

FTEST | =FTEST(array1,array2) | |

LOGINV | =LOGINV(probability,mean,standard_dev) | Returns the inverse of the lognormal cumulative distribution |

LOGNORMDIST | =LOGNORMDIST(x,mean,standard_dev) | Returns the cumulative lognormal distribution |

MODE | ==MODE(number1,number2,…) | Returns the most common value in a data set |

NORMDIST | =NORMDIST(x,mean,standard_dev,cumulative) | Returns the normal cumulative distribution |

NORMINV | =NORMINV(probability,mean,standard_dev) | Returns the inverse of the normal cumulative distribution |

NORMSDIST | =NORMSDIST(z) | Returns the standard normal cumulative distribution |

NORMSINV | =NORMSINV(probability) | Returns the inverse of the standard normal cumulative distribution |

PERCENTILE | =PERCENTILE(array,k) | Returns the k-th percentile of values in a range |

PERCENTRANK | =PERCENTRANK(array,x,significance) | Returns the percentage rank of a value in a data set |

POISSON | =POISSON(x,mean,cumulative) | Returns the Poisson distribution |

QUARTILE | =QUARTILE(array,quart) | Returns the quartile of a data set |

RANK | =RANK(number,ref,order) | Returns the rank of a number in a list of numbers |

STDEV | =STDEV(number1,number2,…) | Estimates standard deviation based on a sample |

STDEVP | =STDEVP(number1,number2,…) | Calculates standard deviation based on the entire population |

TDIST | =TDIST(x,deg_freedom,tails) | Returns the Student’s t-distribution |

TINV | =TINV(probability,deg_freedom) | Returns the inverse of the Student’s t-distribution |

VAR | =VAR(number1,number2,…) | Estimates variance based on a sample |

VARP | =VARP(number1,number2,…) | Calculates variance based on the entire population |

FINV | =FINV(probability,deg_freedom1,deg_freedom2) | Returns the inverse of the F probability distribution |

FORECAST | =FORECAST(x,known_y’s,known_x’s) | Returns a value along a linear trend |

BETADIST | =BETADIST(x,alpha,beta,A,B) | Returns the beta cumulative distribution function |

BETAINV | =BETAINV(probability,alpha,beta,A,B) | Returns the inverse of the cumulative distribution function for a specified beta distribution |

COVAR | =COVAR(array1,array2) | Returns covariance, the average of the products of paired deviations |

CRITBINOM | =CRITBINOM(trials,probability_s,alpha) | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |

EXPONDIST | =EXPONDIST(x,lambda,cumulative) | Returns the exponential distribution |

POISSON | =POISSON(x,mean,cumulative) | Returns the Poisson distribution |

FDIST | =FDIST(x,deg_freedom1,deg_freedom2) | Returns the F probability distribution |

GAMMADIST | =GAMMADIST(x,alpha,beta,cumulative) | Returns the gamma distribution |

GAMMAINV | =GAMMAINV(probability,alpha,beta) | Returns the inverse of the gamma cumulative distribution |

HYPGEOMDIST | =HYPGEOMDIST(sample_s,number_sample,population_s,number_pop) | Returns the hypergeometric distribution |

NEGBINOMDIST | =NEGBINOMDIST(number_f,number_s,probability_s) | Returns the negative binomial distribution |

TTEST | =TTEST(array1,array2,tails,type) | Returns the probability associated with a Student’s t-test |

WEIBULL | =WEIBULL(x,alpha,beta,cumulative) | Calculates variance based on the entire population, including numbers, text, and logical values |

ZTEST | =ZTEST(array,x,sigma) | Returns the one-tailed probability-value of a z-test |

### Cube Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

CUBEKPIMEMBER | =CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption) | Returns a key performance indicator (KPI) name, property, and measure, and displays the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used to monitor an organization’s performance. |

CUBEMEMBER | =CUBEMEMBER(connection,member_expression,caption) | RReturns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube. |

CUBEMEMBERPROPERTY | =CUBEMEMBERPROPERTY(connection,member_expression,property) | Returns the value of a member property in the cube. Use to validate that a member name exists within the cube and to return the specified property for this member. |

CUBERANKEDMEMBER | =CUBERANKEDMEMBER(connection,set_expression,rank,caption) | Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students. |

CUBESET | =CUBESET(connection,set_expression,caption,sort_order,sort_by) | Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel. |

CUBESETCOUNT | =CUBESETCOUNT(set) | Returns the number of items in a set. |

CUBEVALUE | =CUBEVALUE(connection,member_expression1,…) | Returns an aggregated value from a cube |

### Database Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

DGET | =DGET(database,field,criteria) | Extracts from a database a single record that matches the specified criteria |

DSUM | =DSUM(database,field,criteria) | Adds the numbers in the field column of records in the database that match the criteria |

DAVERAGE | =DAVERAGE(database,field,criteria) | Returns the average of selected database entries |

DCOUNT | =DCOUNT(database,field,criteria) | Counts the cells that contain numbers in a database |

DCOUNTA | =DCOUNTA(database,field,criteria) | Counts nonblank cells in a database |

DMAX | =DMAX(database,field,criteria) | Returns the maximum value from selected database entries |

DMIN | =DMIN(database,field,criteria) | Returns the minimum value from selected database entries |

DPRODUCT | =DPRODUCT(database,field,criteria) | Multiplies the values in a particular field of records that match the criteria in a database |

DSTDEV | =DSTDEV(database,field,criteria) | Estimates the standard deviation based on a sample of selected database entries |

DSTDEVP | =DSTDEVP(database,field,criteria) | Calculates the standard deviation based on the entire population of selected database entries |

DVAR | =DVAR(database,field,criteria) | Estimates variance based on a sample from selected database entries |

DVARP | =DVARP(database,field,criteria) | Calculates variance based on the entire population of selected database entries |

### Date & Time Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

DATE | =DATE(year,month,day) | Returns the serial number of a particular date |

DATEVALUE | =DATEVALUE(date_text) | Converts a date in the form of text to a serial number |

DAY | =DAY(serial_number) | Converts a serial number to a day of the month |

HOUR | =HOUR(serial_number) | Converts a serial number to an hour |

MINUTE | =MINUTE(serial_number) | Converts a serial number to a minute |

MONTH | ==MONTH(serial_number) | Converts a serial number to a month |

NOW | =NOW() | Returns the serial number of the current date and time |

SECOND | =SECOND(serial_number) | Converts a serial number to a second |

TIME | =TIME(hour,minute,second) | Returns the serial number of a particular time |

TIMEVALUE | =TIMEVALUE(time_text) | Converts a time in the form of text to a serial number |

TODAY | =TODAY() | Returns the serial number of today’s date |

YEAR | =YEAR(serial_number) | Converts a serial number to a year |

DAYS360 | =DAYS360(start_date,end_date,method) | Calculates the number of days between two dates based on a 360-day year |

EDATE | =EDATE(start_date,months) | Returns the serial number of the date that is the indicated number of months before or after the start date |

EOMONTH | =EOMONTH(start_date,months) | Returns the serial number of the last day of the month before or after a specified number of months |

NETWORKDAYS | =NETWORKDAYS(start_date,end_date,[holidays]) | Returns the number of whole workdays between two dates |

NETWORKDAYS.INTL | =NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) | Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days |

WEEKDAY | =WEEKDAY(serial_number,[return_type]) | Converts a serial number to a day of the week |

WEEKNUM | =WEEKNUM(serial_number,[return_type]) | Converts a serial number to a number representing where the week falls numerically with a year |

WORKDAY | =WORKDAY(start_date, days, [holidays]) | Returns the serial number of the date before or after a specified number of workdays |

WORKDAY.INTL | =WORKDAY.INTL(start_date,days,weekend,holidays) | Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days |

YEARFRAC | =YEARFRAC(start_date,end_date,basis) | Returns the year fraction representing the number of whole days between start_date and end_date |

### Information Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

CELL | =CELL(info_type, [reference]) | Returns information about the formatting, location, or contents of a cell |

ISBLANK | =ISBLANK(value) | Returns TRUE if the value is blank |

ISERROR | =ISERROR(value) | Returns TRUE if the value is any error value |

ISNONTEXT | =ISNONTEXT(value) | Returns TRUE if the value is not text |

ISNUMBER | =ISNUMBER(value) | Returns TRUE if the value is a number |

ISTEXT | =ISTEXT(value) | Returns TRUE if the value is text |

ERROR.TYPE | =ERROR.TYPE(error_val) | Returns a number corresponding to an error type |

INFO | =INFO(type_text) | Returns information about the current operating environment |

ISERR | =ISERR(value) | Returns TRUE if the value is any error value except #N/A |

ISEVEN | =ISEVEN(number) | Returns TRUE if the number is even |

ISLOGICAL | =ISLOGICAL(value) | Returns TRUE if the value is a logical value |

ISNA | =ISNA(value) | Returns TRUE if the value is the #N/A error value |

ISODD | =ISODD(number) | Returns TRUE if the number is odd |

ISREF | =ISREF(value) | Returns TRUE if the value is a reference |

N | =N(value) | Returns a value converted to a number |

NA | =NA() | Returns the error value #N/A |

TYPE | =TYPE(value) | Returns a number indicating the data type of a value |

### Logical Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

AND | =AND(logical1,logical2,…) | Returns TRUE if all of its arguments are TRUE |

FALSE | =FALSE | Returns the logical value FALSE |

IF | =IF(logical_test, [value_if_true], [value_if_false]) | Specifies a logical test to perform |

IFERROR | =IFERROR(value, value_if_error) | Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula |

NOT | =NOT(logical) | Reverses the logic of its argument |

OR | =OR(logical1,logical2,…) | Returns TRUE if any argument is TRUE |

TRUE | =TRUE | Returns the logical value TRUE |

LOOKUP | =LOOKUP(lookup_value, array)– 2 types | Looks up values in a vector or array |

### Lookup & Reference Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

ADDRESS | =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) | Returns a reference as text to a single cell in a worksheet |

COLUMN | =COLUMN([reference]) | Returns the column number of a reference |

COLUMNS | =COLUMNS(array) | Returns the number of columns in a reference |

HLOOKUP | =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]) | Looks in the top row of an array and returns the value of the indicated cell |

INDEX | =INDEX(array,row_num,[column_num])– 2 types | Uses an index to choose a value from a reference or array |

INDIRECT | =INDIRECT(ref_text,a1) | Returns a reference indicated by a text value |

MATCH | =MATCH(lookup_value,lookup_array,match_type) | Looks up values in a reference or array |

OFFSET | =OFFSET(reference,rows,cols,height,width) | Returns a reference offset from a given reference |

ROW | =ROW([reference]) | Returns the row number of a reference |

ROWS | =ROWS(array) | Returns the number of rows in a reference |

VLOOKUP | =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) | Looks in the first column of an array and moves across the row to return the value of a cell |

CHOOSE | =CHOOSE(index_num,value1,value2,…) | Chooses a value from a list of values |

GETPIVOTDATA | =GETPIVOTDATA(data_field,pivot_table,field,item,…) | Returns data stored in a PivotTable report |

HYPERLINK | =HYPERLINK(link_location,friendly_name) | Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet |

TRANSPOSE | =TRANSPOSE(array) | Returns the transpose of an array |

AREAS | =AREAS(reference) | Returns the number of areas in a reference |

RTD | =RTD(progID,server,topic1,topic2,…) | Retrieves real-time data from a program that supports COM automation (Automation: A way to work with an application’s objects from another application or development tool. Formerly called OLE Automation, Automation is an industry-standard and a feature of the Component Object Model (COM).) |

### Text Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

EXACT | =EXACT(text1,text2) | Checks to see if two text values are identical |

LOWER | =LOWER(text) | Converts text to lowercase |

PROPER | =PROPER(text) | Capitalizes the first letter in each word of a text value |

TRIM | =TRIM(text) | Removes spaces from text |

UPPER | =UPPER(text) | Converts text to uppercase |

CHAR | =CHAR(number) | Returns the character specified by the code number |

CLEAN | =CLEAN(text) | Removes all nonprintable characters from text |

CODE | =CODE(text) | Returns a numeric code for the first character in a text string |

DOLLAR | =DOLLAR(number,decimals) | Converts a number to text, using the $ (dollar) currency format |

FIXED | =FIXED(number,decimals,no_commas) | Formats a number as text with a fixed number of decimals |

PHONETIC | =PHONETIC(reference) | Extracts the phonetic (furigana) characters from a text string |

REPT | =REPT(text,number_times) | Repeats text a given number of times |

SUBSTITUTE | =SUBSTITUTE(text,old_text,new_text,instance_num) | Substitutes new text for old text in a text string |

T | =T(value) | Converts its arguments to text |

VALUE | =VALUE(text) | Converts a text argument to a number |

ASC | =ASC(text) | Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters |

BAHTTEXT | =BAHTTEXT(number) | Converts a number to text, using the ß (baht) currency format |

### Most Common Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

FIND | =FIND(find_text,within_text,start_num) | Finds one text value within another (case-sensitive) |

LEFT | =LEFT(text,num_chars) | Returns the leftmost characters from a text value |

LEN | =LEN(text) | Returns the number of characters in a text string |

MID | =MID(text,start_num,num_chars) | Returns a specific number of characters from a text string starting at the position you specify |

REPLACE | =REPLACE(old_text,start_num,num_chars,new_text) | Replaces characters within text |

RIGHT | =RIGHT(text,num_chars) | Returns the rightmost characters from a text value |

SEARCH | =SEARCH(find_text,within_text,start_num) | Finds one text value within another (not case-sensitive) |

### Engineering Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

CONVERT | =CONVERT(number,from_unit,to_unit) | Converts a number from one measurement system to another |

DELTA | =DELTA(number1,number2) | Tests whether two values are equal |

ERF | =ERF(lower_limit,upper_limit) | Returns the error function |

ERFC | =ERFC(x) | Returns the complementary error function |

GESTEP | =GESTEP(number,step) | Tests whether a number is greater than a threshold value |

ERF.PRECISE | =ERF.PRECISE(X) | Returns the error function |

ERFC.PRECISE | =ERFC.PRECISE(X) | Returns the complementary ERF function integrated between x and infinity |

BESSELI | =BESSELI(x,n) | Returns the modified Bessel function In(x) |

BESSELJ | =BESSELJ(x,n) | Returns the Bessel function Jn(x) |

BESSELK | =BESSELK(x,n) | Returns the modified Bessel function Kn(x) |

BESSELY | =BESSELY(x,n) | Returns the Bessel function Yn(x) |

BIN2DEC | =BIN2DEC(number) | Converts a binary number to decimal |

BIN2HEX | =BIN2HEX(number,places) | Converts a binary number to hexadecimal |

DEC2OCT | =DEC2OCT(number,places) | Converts a decimal number to octal |

HEX2BIN | =HEX2BIN(number,places) | Converts a hexadecimal number to binary |

HEX2DEC | =HEX2DEC(number) | Converts a hexadecimal number to decimal |

HEX2OCT | =HEX2OCT(number,places) | Converts a hexadecimal number to octal |

IMABS | =IMABS(inumber) | Returns the absolute value (modulus) of a complex number |

IMAGINARY | =IMAGINARY(inumber) | Returns the imaginary coefficient of a complex number |

IMARGUMENT | =IMARGUMENT(inumber) | Returns the argument theta, an angle expressed in radians |

IMCONJUGATE | =IMCONJUGATE(inumber) | Returns the complex conjugate of a complex number |

IMCOS | =IMCOS(inumber) | Returns the cosine of a complex number |

IMDIV | =IMDIV(inumber1,inumber2) | Returns the quotient of two complex numbers |

IMEXP | =IMEXP(inumber) | Returns the exponential of a complex number |

IMLN | =IMLN(inumber) | Returns the natural logarithm of a complex number |

IMLOG10 | =IMLOG10(inumber) | Returns the base-10 logarithm of a complex number |

IMLOG2 | =IMLOG2(inumber) | Returns the base-2 logarithm of a complex number |

IMPOWER | =IMPOWER(inumber,number) | Returns a complex number raised to an integer power |

IMPRODUCT | =IMPRODUCT(inumber1,inumber2,…) | Returns the product of complex numbers |

IMREAL | =IMREAL(inumber) | Returns the real coefficient of a complex number |

IMSIN | =IMSIN(inumber) | Returns the sine of a complex number |

IMSQRT | =IMSQRT(inumber) | Returns the square root of a complex number |

IMSUB | =IMSUB(inumber1,inumber2) | Returns the difference between two complex numbers |

IMSUM | =IMSUM(inumber1,inumber2,…) | Returns the sum of complex numbers |

OCT2BIN | =OCT2BIN(number,places) | Converts an octal number to binary |

OCT2DEC | =OCT2DEC(number) | Converts an octal number to decimal |

OCT2HEX | =OCT2HEX(number,places) | Converts an octal number to hexadecimal |

### Financial Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

AMORDEGRC | =AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis) | Returns the depreciation for each accounting period by using a depreciation coefficient |

AMORLINC | =AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis) | Returns the depreciation for each accounting period |

DOLLARDE | =DOLLARDE(fractional_dollar,fraction) | Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number |

DOLLARFR | =DOLLARFR(decimal_dollar,fraction) | Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction |

SLN | =SLN(cost,salvage,life) | Returns the straight-line depreciation of an asset for one period |

SYD | =SYD(cost,salvage,life,per) | Returns the sum-of-years’ digits depreciation of an asset for a specified period |

DB | =DB(cost,salvage,life,period,month) | Returns the depreciation of an asset for a specified period by using the fixed-declining balance method |

DDB | =DDB(cost,salvage,life,period,factor) | Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify |

EFFECT | =EFFECT(nominal_rate,npery) | Returns the effective annual interest rate |

FV | =FV(rate,nper,pmt,pv,type) | Returns the future value of an investment |

IPMT | =IPMT(rate,per,nper,pv,fv,type) | Returns the interest payment for an investment for a given period |

IRR | =IRR(values,guess) | Returns the internal rate of return for a series of cash flows |

MIRR | =MIRR(values,finance_rate,reinvest_rate) | Returns the internal rate of return where positive and negative cash flows are financed at different rates |

NOMINAL | =NOMINAL(effect_rate,npery) | Returns the annual nominal interest rate |

NPER | =NPER(rate,pmt,pv,fv,type) | Returns the number of periods for an investment |

NPV | =NPV(rate,value1,value2,…) | Returns the net present value of an investment based on a series of periodic cash flows and a discount rate |

PV | =PV(rate,nper,pmt,fv,type) | Returns the present value of an investment |

RATE | =RATE(nper,pmt,pv,fv,type,guess) | Returns the interest rate per period of an annuity |

YIELD | =YIELD(settlement,maturity,rate,pr,redemption,frequency,basis) | Returns the yield on a security that pays periodic interest |

ACCRINT | =ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis,calc_method) | Returns the accrued interest for a security that pays periodic interest |

ACCRINTM | =ACCRINTM(issue,settlement,rate,par,basis) | Returns the accrued interest for a security that pays interest at maturity |

COUPDAYBS | =COUPDAYBS(settlement,maturity,frequency,basis) | Returns the number of days from the beginning of the coupon period to the settlement date |

COUPDAYS | =COUPDAYS(settlement,maturity,frequency,basis) | Returns the number of days in the coupon period that contains the settlement date |

COUPDAYSNC | =COUPDAYSNC(settlement,maturity,frequency,basis) | Returns the number of days from the settlement date to the next coupon date |

COUPNCD | =COUPNCD(settlement,maturity,frequency,basis) | Returns the next coupon date after the settlement date |

COUPNUM | =COUPNUM(settlement,maturity,frequency,basis) | Returns the number of coupons payable between the settlement date and maturity date |

COUPPCD | =COUPPCD(settlement,maturity,frequency,basis) | Returns the previous coupon date before the settlement date |

CUMIPMT | =CUMIPMT(rate,nper,pv,start_period,end_period,type) | Returns the cumulative interest paid between two periods |

CUMPRINC | =CUMPRINC(rate,nper,pv,start_period,end_period,type) | Returns the cumulative principal paid on a loan between two periods |

DISC | =DISC(settlement,maturity,pr,redemption,basis) | Returns the discount rate for a security |

DURATION | =DURATION(settlement,maturity,coupon,yld,frequency,basis) | Returns the annual duration of a security with periodic interest payments |

FVSCHEDULE | =FVSCHEDULE(principal,schedule) | Returns the future value of an initial principal after applying a series of compound interest rates |

INTRATE | =INTRATE(settlement,maturity,investment,redemption,basis) | Returns the interest rate for a fully invested security |

ISPMT | =ISPMT(rate,per,nper,pv) | Calculates the interest paid during a specific period of an investment |

MDURATION | =MDURATION(settlement,maturity,coupon,yld,frequency,basis) | Returns the Macauley modified duration for a security with an assumed par value of $100 |

ODDFPRICE | =ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld,redemption,frequency,basis) | Returns the price per $100 face value of a security with an odd first period |

ODDFYIELD | =ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,basis) | Returns the yield of a security with an odd first period |

ODDLPRICE | =ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,basis) | Returns the price per $100 face value of a security with an odd last period |

ODDLYIELD | =ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis) | Returns the yield of a security with an odd last period |

PMT | =PMT(rate,nper,pv,fv,type) | Returns the periodic payment for an annuity |

PPMT | =PPMT(rate,per,nper,pv,fv,type) | Returns the payment on the principal for an investment for a given period |

PRICE | =PRICE(settlement,maturity,rate,yld,redemption,frequency,basis) | Returns the price per $100 face value of a security that pays periodic interest |

PRICEDISC | =PRICEDISC(settlement,maturity,discount,redemption,basis) | Returns the price per $100 face value of a discounted security |

PRICEMAT | =PRICEMAT(settlement,maturity,issue,rate,yld,basis) | Returns the price per $100 face value of a security that pays interest at maturity |

RECEIVED | =RECEIVED(settlement,maturity,investment,discount,basis) | Returns the amount received at maturity for a fully invested security |

TBILLEQ | =TBILLEQ(settlement,maturity,discount) | Returns the bond-equivalent yield for a Treasury bill |

TBILLPRICE | =TBILLPRICE(settlement,maturity,discount) | Returns the price per $100 face value for a Treasury bill |

TBILLYIELD | =TBILLYIELD(settlement,maturity,pr) | Returns the yield for a Treasury bill |

VDB | =VDB(cost,salvage,life,start_period,end_period,factor,no_switch) | Returns the depreciation of an asset for a specified or partial period by using a declining balance method |

XIRR | =XIRR(values,dates,guess) | Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic |

XNPV | =XNPV(rate,values,dates) | Returns the net present value for a schedule of cash flows that is not necessarily periodic |

YIELDDISC | =YIELDDISC(settlement,maturity,pr,redemption,basis) | Returns the annual yield for a discounted security; for example, a Treasury bill |

YIELDMAT | =YIELDMAT(settlement,maturity,issue,rate,pr,basis) | Returns the annual yield of a security that pays interest at maturity |

### Math and Trigonometry Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

ABS | =ABS(number) | Returns the absolute value of a number |

PRODUCT | =PRODUCT(number1,number2,…) | Multiplies its arguments |

RAND | =RAND() | Returns a random number between 0 and 1 |

RANDBETWEEN | =RANDBETWEEN(bottom,top) | Returns a random number between the numbers you specify |

ROUND | =ROUND(number,num_digits) | Rounds a number to a specified number of digits |

ROUNDDOWN | =ROUNDDOWN(number,num_digits) | Rounds a number down, toward zero |

ROUNDUP | =ROUNDUP(number,num_digits) | Rounds a number up, away from zero |

SUBTOTAL | =SUBTOTAL(function_num,ref1,…) | Returns a subtotal in a list or database |

SUM | =SUM(number1,number2,…) | Adds its arguments |

SUMIF | =SUMIF(range,criteria,[sum_range]) | Adds the cells specified by a given criteria |

SUMIFS | =SUMIFS(sum_range,criteria_range,criteria,…) | Adds the cells in a range that meet multiple criteria |

SUMPRODUCT | =SUMPRODUCT(array1,array2,[array3],…) | Returns the sum of the products of corresponding array components |

CEILING | =CEILING(number,significance) | Rounds a number to the nearest integer or to the nearest multiple of significance |

CEILING.PRECISE | =CEILING.PRECISE(number,significance) | Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. |

EVEN | =EVEN(number) | Rounds a number up to the nearest even integer |

EXP | =EXP(number) | Returns e raised to the power of a given number |

FACT | =FACT(number) | Returns the factorial of a number |

FLOOR.PRECISE | =FLOOR.PRECISE(number,significance) | Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up. |

GCD | =GCD(number1,number2,…) | Returns the greatest common divisor |

INT | =INT(number) | Rounds a number down to the nearest integer |

ISO.CEILING | =ISO.CEILING(number,significance) | Returns a number that is rounded up to the nearest integer or to the nearest multiple of significance |

LCM | =LCM(number1,number2,…) | Returns the least common multiple |

MOD | =MOD(number,divisor) | Returns the remainder from division |

MROUND | =MROUND(number,multiple) | Returns a number rounded to the desired multiple |

ODD | =ODD(number) | Rounds a number up to the nearest odd integer |

PI | =PI() | Returns the value of pi |

POWER | =POWER(number,power) | Returns the result of a number raised to a power |

QUOTIENT | =QUOTIENT(numerator,denominator) | Returns the integer portion of a division |

SERIESSUM | =SERIESSUM(x,n,m,coefficients) | Returns the sum of a power series based on the formula |

SIGN | =SIGN(number) | Returns the sign of a number |

SQRT | =SQRT(number) | Returns a positive square root |

SUMSQ | =SUMSQ(number1,number2,…) | Returns the sum of the squares of the arguments |

TRUNC | =TRUNC(number,num_digits) | Truncates a number to an integer |

AGGREGATE | =AGGREGATE(function_num,options,array,k) | Returns an aggregate in a list or database |

COMBIN | =COMBIN(number,number_chosen) | Returns the number of combinations for a given number of objects |

COS | =COS(number) | Returns the cosine of a number |

COSH | =COSH(number) | Returns the hyperbolic cosine of a number |

FACTDOUBLE | =FACTDOUBLE(number) | Returns the double factorial of a number |

LN | =LN(number) | Returns the natural logarithm of a number |

LOG | =LOG(number,base) | Returns the logarithm of a number to a specified base |

LOG10 | =LOG10(number) | Returns the base-10 logarithm of a number |

MULTINOMIAL | =MULTINOMIAL(number1,number2,…) | Returns the multinomial of a set of numbers |

SIN | =SIN(number) | Returns the sine of the given angle |

SINH | =SINH(number) | Returns the hyperbolic sine of a number |

SUMX2MY2 | =SUMX2MY2(array_x,array_y) | Returns the sum of the difference of squares of corresponding values in two arrays |

SUMX2PY2 | =SUMX2PY2(array_x,array_y) | Returns the sum of the sum of squares of corresponding values in two arrays |

SUMXMY2 | =SUMXMY2(array_x,array_y) | Returns the sum of squares of differences of corresponding values in two arrays |

TAN | =TAN(number) | Returns the tangent of a number |

TANH | =TANH(number) | Returns the hyperbolic tangent of a number |

ACOS | =ACOS(number) | Returns the arccosine of a number |

ACOSH | =ACOSH(number) | Returns the inverse hyperbolic cosine of a number |

ASIN | =ASIN(number) | Returns the arcsine of a number |

ASINH | =ASINH(number) | Returns the inverse hyperbolic sine of a number |

ATAN | =ATAN(number) | Returns the arctangent of a number |

ATAN2 | =ATAN2(x_num,y_num) | Returns the arctangent from x- and y-coordinates |

ATANH | =ATANH(number) | Returns the inverse hyperbolic tangent of a number |

DEGREES | =DEGREES(angle) | Converts radians to degrees |

MDETERM | =MDETERM(array) | Returns the matrix determinant of an array |

MINVERSE | =MINVERSE(array) | Returns the matrix inverse of an array |

MMULT | =MMULT(array1,array2) | Returns the matrix product of two arrays |

RADIANS | =RADIANS(angle) | Converts degrees to radians |

ROMAN | =ROMAN(number,form) | Converts an arabic numeral to roman, as text |

SQRTPI | =SQRTPI(number) | Returns the square root of (number * pi) |

### Statistical Excel Formulas & Functions

Functions | Excel Formulas | Description |
---|---|---|

AVERAGE | =AVERAGE(number1,number2,…) | Returns the average of its arguments |

AVERAGEIF | =AVERAGEIF(range,criteria,[average_range]) | Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria |

COUNT | =COUNT(value1,value2,…) | Counts how many numbers are in the list of arguments |

COUNTA | =COUNTA(value1,value2,…) | Counts how many values are in the list of arguments |

COUNTBLANK | =COUNTBLANK(range) | Counts the number of blank cells within a range |

COUNTIF | =COUNTIF(range,criteria) | Counts the number of cells within a range that meet the given criteria |

COUNTIFS | =COUNTIFS(criteria_range,criteria,…) | Counts the number of cells within a range that meet multiple criteria |

MAX | =MAX(number1,number2,…) | Returns the maximum value in a list of arguments |

MEDIAN | =MEDIAN(number1,number2,…) | Returns the median of the given numbers |

MIN | =MIN(number1,number2,…) | Returns the minimum value in a list of arguments |

TEXT | =TEXT(value,format_text) | Formats a number and converts it to text |

AVERAGEA | =AVERAGEA(value1,value2,…) | Returns the average of its arguments, including numbers, text, and logical values |

AVERAGEIFS | =AVERAGEIFS(average_range,criteria_range,criteria,…) | Returns the average (arithmetic mean) of all cells that meet multiple criteria |

GEOMEAN | =GEOMEAN(number1,number2,…) | Returns the geometric mean |

INTERCEPT | =INTERCEPT(known_y’s,known_x’s) | Returns the intercept of the linear regression line |

LARGE | =LARGE(array,k) | Returns the k-th largest value in a data set |

LINEST | =LINEST(known_y’s,known_x’s,const,stats) | Returns the parameters of a linear trend |

LOGEST | =LOGEST(known_y’s,known_x’s,const,stats) | Returns the parameters of an exponential trend |

MAXA | =MAXA(value1,value2,…) | Returns the maximum value in a list of arguments, including numbers, text, and logical values |

MINA | =MINA(value1,value2,…) | Returns the smallest value in a list of arguments, including numbers, text, and logical values |

MODE.MULT | =MODE.MULT(number1,number2,…) | Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data |

MODE.SNGL | =MODE.SNGL(number1,number2,…) | Returns the most common value in a data set |

PROB | =PROB(x_range,prob_range,lower_limit,upper_limit) | Returns the probability that values in a range are between two limits |

RANK.AVG | =RANK.AVG(number,ref,order) | Returns the rank of a number in a list of numbers |

RANK.EQ | =RANK.EQ(number,ref,order) | Returns the rank of a number in a list of numbers |

SKEW | =SKEW(number1,number2,…) | Returns the skewness of a distribution |

SLOPE | =SLOPE(known_y’s,known_x’s) | Returns the slope of the linear regression line |

SMALL | =SMALL(array,k) | Returns the k-th smallest value in a data set |

STANDARDIZE | =STANDARDIZE(x,mean,standard_dev) | Returns a normalized value |

TREND | =TREND(known_y’s,known_x’s,new_x’s,const) | Returns values along a linear trend |

NORM.S.INV | =NORM.S.INV(probability) | Returns the inverse of the standard normal cumulative distribution |

AVEDEV | =AVEDEV(number1,number2,…) | Returns the average of the absolute deviations of data points from their mean |

BETA.DIST | =BETA.DIST(x,alpha,beta,cumulative,A,B) | Returns the beta cumulative distribution function |

BETA.INV | =BETA.INV(probability,alpha,beta,A,B) | Returns the inverse of the cumulative distribution function for a specified beta distribution |

BINOM.DIST | =BINOM.DIST(number_s,trials,probability_s,cumulative) | Returns the individual term binomial distribution probability |

BINOM.INV | =BINOM.INV(trials,probability_s,alpha) | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |

CHISQ.DIST | =CHISQ.DIST(x,deg_freedom,cumulative) | Returns the cumulative beta probability density function |

CHISQ.DIST.RT | =CHISQ.DIST.RT(x,deg_freedom) | Returns the one-tailed probability of the chi-squared distribution |

CHISQ.INV | =CHISQ.INV(probability,deg_freedom) | Returns the cumulative beta probability density function |

CHISQ.INV.RT | =CHISQ.INV.RT(probability,deg_freedom) | Returns the inverse of the one-tailed probability of the chi-squared distribution |

CHISQ.TEST | =CHISQ.TEST(actual_range,expected_range) | Returns the test for independence |

CONFIDENCE.NORM | =CONFIDENCE.NORM(alpha,standard_dev,size) | Returns the confidence interval for a population mean |

CONFIDENCE.T | =CONFIDENCE.T(alpha,standard_dev,size) | Returns the confidence interval for a population mean, using a Student’s t distribution |

CORREL | =CORREL(array1,array2) | Returns the correlation coefficient between two data sets |

COVARIANCE.P | =COVARIANCE.P(array1,array2) | Returns covariance, the average of the products of paired deviations |

COVARIANCE.S | =COVARIANCE.S(array1,array2) | Returns the sample covariance, the average of the products deviations for each data point pair in two data sets |

DEVSQ | =DEVSQ(number1,number2,…) | Returns the sum of squares of deviations |

EXPON.DIST | =EXPON.DIST(x,lambda,cumulative) | Returns the exponential distribution |

F.DIST | =F.DIST(x,deg_freedom1,deg_freedom2,cumulative) | Returns the F probability distribution |

F.DIST.RT | =F.DIST.RT(x,deg_freedom1,deg_freedom2) | Returns the F probability distribution |

F.INV | =F.INV(probability,deg_freedom1,deg_freedom2) | Returns the inverse of the F probability distribution |

F.INV.RT | =F.INV.RT(probability,deg_freedom1,deg_freedom2) | Returns the inverse of the F probability distribution |

F.TEST | =F.TEST(array1,array2) | Returns the result of an F-test |

FISHER | =FISHER(x) | Returns the Fisher transformation |

FISHERINV | =FISHERINV(y) | Returns the inverse of the Fisher transformation |

FREQUENCY | =FREQUENCY(data_array,bins_array) | Returns a frequency distribution as a vertical array |

GAMMA.DIST | =GAMMA.DIST(x,alpha,beta,cumulative) | Returns the gamma distribution |

GAMMA.INV | =GAMMA.INV(probability,alpha,beta) | Returns the inverse of the gamma cumulative distribution |

GAMMALN | =GAMMALN(x) | Returns the natural logarithm of the gamma function, G(x) |

GAMMALN.PRECISE | =GAMMALN.PRECISE(x) | Returns the natural logarithm of the gamma function, G(x) |

GROWTH | =GROWTH(known_y’s,known_x’s,new_x’s,const) | Returns values along an exponential trend |

HARMEAN | =HARMEAN(number1,number2,…) | Returns the harmonic mean |

HYPGEOM.DIST | =HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative) | Returns the hypergeometric distribution |

KURT | =KURT(number1,number2,…) | Returns the kurtosis of a data set |

LOGNORM.DIST | =LOGNORM.DIST(x,mean,standard_dev,cumulative) | Returns the cumulative lognormal distribution |

LOGNORM.INV | =LOGNORM.INV(probability,mean,standard_dev) | Returns the inverse of the lognormal cumulative distribution |

NEGBINOM.DIST | =NEGBINOM.DIST(number_f,number_s,probability_s,cumulative) | Returns the negative binomial distribution |

NORM.DIST | =NORM.DIST(x,mean,standard_dev,cumulative) | Returns the normal cumulative distribution |

NORM.INV | =NORM.INV(probability,mean,standard_dev) | Returns the inverse of the normal cumulative distribution |

NORM.S.DIST | =NORM.S.DIST(z,cumulative) | Returns the standard normal cumulative distribution |

PEARSON | =PEARSON(array1,array2) | Returns the Pearson product moment correlation coefficient |

PERCENTILE.EXC | =PERCENTILE.EXC(array,k) | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive |

PERCENTILE.INC | =PERCENTILE.INC(array,k) | Returns the k-th percentile of values in a range |

PERCENTRANK.EXC | =PERCENTRANK.EXC(array,x,significance) | Returns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set |

PERCENTRANK.INC | =PERCENTRANK.INC(array,x,significance) | Returns the percentage rank of a value in a data set |

PERMUT | =PERMUT(number,number_chosen) | Returns the number of permutations for a given number of objects |

POISSON.DIST | =POISSON.DIST(x,mean,cumulative) | Returns the Poisson distribution |

QUARTILE.EXC | =QUARTILE.EXC(array,quart) | Returns the quartile of the data set, based on percentile values from 0..1, exclusive |

QUARTILE.INC | =QUARTILE.INC(array,quart) | Returns the quartile of a data set |

RSQ | =RSQ(known_y’s,known_x’s) | Returns the square of the Pearson product moment correlation coefficient |

STDEV.P | =STDEV.P(number1,number2,…) | Calculates standard deviation based on the entire population |

STDEV.S | =STDEV.S(number1,number2,…) | Estimates standard deviation based on a sample |

STDEVA | =STDEVA(value1,value2,…) | Estimates standard deviation based on a sample, including numbers, text, and logical values |

STDEVPA | =STDEVPA(value1,value2,…) | Calculates standard deviation based on the entire population, including numbers, text, and logical values |

STEYX | =STEYX(known_y’s,known_x’s) | Returns the standard error of the predicted y-value for each x in the regression |

T.DIST | =T.DIST(x,deg_freedom,cumulative) | Returns the Percentage Points (probability) for the Student t-distribution |

T.DIST.2T | =T.DIST.2T(x,deg_freedom) | Returns the Percentage Points (probability) for the Student t-distribution |

T.DIST.RT | =T.DIST.RT(x,deg_freedom) | Returns the Student’s t-distribution |

T.INV | =T.INV(probability,deg_freedom) | Returns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom |

T.INV.2T | =T.INV.2T(probability,deg_freedom) | Returns the inverse of the Student’s t-distribution |

T.TEST | =T.TEST(array1,array2,tails,type) | Returns the probability associated with a Student’s t-test |

TRIMMEAN | =TRIMMEAN(array,percent) | Returns the mean of the interior of a data set |

VAR.P | =VAR.P(number1,number2,…) | Calculates variance based on the entire population |

VAR.S | =VAR.S(number1,number2,…) | Estimates variance based on a sample |

VARA | =VARA(value1,value2,…) | Estimates variance based on a sample, including numbers, text, and logical values |

VARPA | =VARPA(value1,value2,…) | Calculates variance based on the entire population, including numbers, text, and logical values |

WEIBULL.DIST | =WEIBULL.DIST(x,alpha,beta,cumulative) | Returns the Weibull distribution |

Z.TEST | =Z.TEST(array,x,sigma) | Returns the one-tailed probability-value of a z-test |

## Excel Fomulas PDF & Functions

A formulas in Excel is a mathematical expression that retures specific result. List of some useful**Excel Fomulas & Functions**as Follows:

- = 1+ 3 // returns 4
- = 9 / 3 // returns 3
- = SUM(1,3,5) // returns 9
- = SUM(A1:A3) // returns A1+A2+A3
- = AVERAGE(3,4,5) //returns 6
- = MIN(2,5,4) // returns 2
- = MAX(2,5,4) // returns 5
- =COUNTIF (range, criteria) // two arguments

Share