# 51 New Excel 2013 Functions

*Excel 2013 promises greater functionality and the ability to more quickly input and analyze data. Although this version of Excel uses some of the same functions as older versions, you should learn the 51 new functions to make sure you don’t miss out on something that could become a favorite resource. In addition, some older functions may become obsolete as Microsoft continues to create updated versions of Excel. Check out the latest Office has to offer with the following new functions.*

*You can also download an XLSX version here, comparing the Excel descriptions of each function to my descriptions and examples.*

**Date and Time Functions**

**DAYS** returns the number of days between two dates. For example, if you input that you received a payment on 1/01/2013 and another payment on 1/13/2013, the DAYS function tells you that the payments were spaced 12 days apart.

**ISOWEEKNUM** returns the week number according to ISO standards. Followed by most European countries, the International Organization for Standardization (ISO 8601) states that all weeks begin on a Monday. Further, the first week of the year starts with the week that the first Thursday and January 4th fall within in. For example, 1/01/2012 is part of week 52 of 2011. Week 1 of 2012 begins on 1/02/2012 (Monday) and ends on 1/08/2012 (Sunday). Compare this function to the WEEKDAY function to gain a better understanding.

**Engineering Functions**

**BITAND** runs the bitwise operation AND on two numbers. It converts them into binary code and returns 1 if and only if both numbers convert to 1 in binary; otherwise, it returns 0.

**BITOR** runs the bitwise operation OR, which sets a bit to 1 if it finds any 1’s in the two bits and sets the bit to 0 if not. For example, BITOR will set the two bits 1 & 0 to 1 and 0 & 0 to 0.

**BITXOR**, which is short for EXCLUSIVE OR, compares two bits and sets them to 1 if they are different and 0 if they are the same. For example, the bits 1 & 0 become 1 while 1 & 1 become 0.

**BITRSHIFT** shifts bits to the right by a specified amount. For example, shifting by 1 moves all the bits in a sequence over to the right by 1, so that 000110 becomes 000011.

**BITLSHIFT** shifts bits to the left by a specified amount. For example, shifting by 1 moves all the bits in a sequence over to the left by 1 so that 000110 becomes 001100.

**IMCOSH** returns the hyperbolic cosine of a complex number. For example, if you want to get the hyperbolic cosine of 5 + 3i, running this function returns a result including both real and imaginary numbers. You must run this function in the format =IMCOSH(“5+3i”).

**IMCOT** returns the hyperbolic cotangent of a complex number. You could change IMCOSH to IMCOT to calculate the hyperbolic cotangent of 5 + 3i.

**IMCSC** provides the cosecant of a complex number. Note: this is not the hyperbolic cosecant; it is the trigonometric cosecant.

**IMCSCH** provides the hyperbolic cosecant of a complex number. If you need this function, make sure you add the H on the end of the function so that you get the hyperbolic cosecant rather than the trigonometric cosecant.

**IMSEC** provides the trigonometric secant of a complex number. As with cosecants, make sure you are using the correct function in order to get the type of secant you want.

**IMSECH** provides the hyperbolic secant of a complex number. You can run this function by adding an H onto the end of the trigonometric function and rerunning. For example, if you had calculated the trigonometric secant of 5 + 3i by typing =IMSEC(“5+3i”) you could then run the hyperbolic secant by changing IMSEC to IMSECH.

**IMSINH** calculates the hyperbolic sine of a complex number. As with all the other hyperbolic functions, simply place the expression in quotes that you want to run. For example, you could type =IMSINH(“5+3i”).

**IMTAN** calculates the tangent of a complex number, such as the tangent of 5+3i.

**Financial Functions**

**PDURATION** calculates how many periods are needed for an investment to reach a specific value. For example, if you want to know how long it’ll take for your investment (present value) to be worth $10,000 (future value), PDURATION can calculate how many periods you can expect to have to wait (depending on the interest rate).

**RRI** is the complementary value to PDURATION. Instead of calculating how my periods are needed for the investment to reach a specific value, it calculates the interest rate on the investment if you know how long it will take to reach the value. For example, if PDURATION calculates that it will take 10 years for your investment to grow from $5,000 to $10,000, RRI can calculate an interest rate of 7.2 percent.

**Information Functions**

**ISFORMULA** checks whether a specific cell contains a formula. If yes, the result is “TRUE”; and if no, the result is “FALSE.”

**SHEET** tells you which number sheet you are working with. For example, if you have 10 worksheets in a workbook and are working with the second worksheet created, SHEET will return a value of 2.

**SHEETS** tells you how many sheets are in the workbook you are working with. For example, if there are 10 worksheets in the workbook, the SHEETS function will return a value of 10.

**Logical Functions**

**IFNA** returns a specific value that you set if an expression resolves to N/A. Otherwise, it returns the expression’s value. For example, if you have a spreadsheet that has listings for different cities, you can use an IFNA function to return the expression, “Sorry, not found” if you search for a city that is not in the database using VLOOKUP.

**XOR** returns TRUE if one of the conditions in is true, otherwise it will return FALSE. For example, if you use XOR to test whether your sales are greater than $500, your profit is more than 20 percent of your sales and you have at least 20 new customers this month, you will get a response of TRUE if only 1 of those criteria is met.

**Lookup and Reference Functions**

**FORMULATEXT** shows you the formula that is listed in a particular cell so that you can see it and check for any errors. For example, if you run =FORMULATEXT(R1) in cell R4, it puts the formula found at R1 into R4.

**Math and Trigonometry Functions**

**ACOT** calculates the arccotangent of a particular number. You need to use real numbers to use this function. For example, if the cotangent is 2, you can run ACOT(2) to find out the arccotangent. ACOT calculates arccotangents in radians.

**ACOTH** calculates the hyperbolic arccotangent rather than the standard arccotangent. For example, you can use ACOTH(2) to find the hyperbolic arccotangent of 2.

**ARABIC** converts numerals from Roman to Arabic. For example, you can run ARABIC(XIII) to get the result of “13.” You can also tell ARABIC to convert the contents of a cell into Arabic numerals For example, if cell A2 contains the number CXLIII, you can run ARABIC(A2) to get the result “143.”

**BASE** converts hexadecimal numbers into numbers of whatever base you want with a specified minimum length. For example, you can convert hexidecimal numbers into base 2 with a minimum length of 10 using this function.

**CEILING.MATH** rounds numbers up according you your specifications. You can either round numbers up to the nearest integer or the nearest multiple. For example, you can round 8.76 up to 9 (the nearest integer) or up to the nearest multiple of 5, which would be 10. You can also specify whether you want to round negative numbers towards zero or away from zero.

**COMBINA** returns the number of combinations for a given number, including repetitions. For example, if you want to know how many three-letter combinations of a five-letter set are possible, you would run COMBINA (5,3) to get an answer of 35.

**COT** gives you the cotangent of an angle in radians. You need to know the measurement of the angle in radians to run this function. For example, you can run COT(5) to find out the cotangent in radians of a 5-radian angle.

**COTH** gives you the hyperbolic cotangent of a hyperbolic angle. For example, if your hyperbolic angle is 5, run COTH(5). Be careful not to mix COTH and COT up as they are different types of cotangents.

**CSC** calculates the cosecant in radians of an angle. Make sure that you convert the angle to radians if you measure it in degrees. For example, if you have a 142 degree angle, convert it to 2.478 radians before running CSC(2.478) to find the cosecant.

**CSCH** calculates the hyperbolic cosecant of an angle in radians. Run CSCH(angle) to get this result; make sure you only run CSCH if you want the hyperbolic cosecant, not the standard cosecant.

**DECIMAL** converts a number into hexadecimal format. You need to know the base the number is written in so that you can convert it appropriately. For example, you can convert FF from base 16 to the hexadecimal equivalent, which is 255, using this function.

**FLOOR.MATH** is the opposite of CEILING.MATH; it rounds numbers *down* to the nearest integer or multiple. For example, you could round 8.142 down to 8 or down to 6 (the nearest multiple of 3) using FLOOR.MATH.

**ISO.CEILING** is similar to CEILING.MATH but does not take sign into account. For example, with CEILING.MATH you can ask it to round -3.41 further away from zero (-4) because of the sign. ISO.CEILING will round this number to -3 unless you specify a multiple such as nearest multiple of 2, in which case it will round up to -2.

**MUNIT** displays a matrix of a dimension you specify, which needs to be entered as an array. For example, MUNIT(3) returns a 3x3 matrix.

**SEC** calculates the secant of an angle in radians. For example, you can run SEC(2.478) to find the secant of an angle that measures 2.478 radians.

**SECH** calculates the hyperbolic secant of an angle in radians. Use this function only for hyperbolic, not standard, secants of angles.

**Statistical Functions**

**BINOM.DIST.RANGE** calculates the statistical probability of an outcome based on the results of a trial, using a binomial distribution curve. To perform this calculation, you need to know how many trials were performed and how many were successful. You also need to know the probability of success. For example, if your trial was flipping a coin 50 times to try to get heads and you got 10 heads, you would run BINOM.DIST.RANGE(50,.5,10).

**GAMMA** returns the gamma value of a number. For example, GAMMA(132) calculates the gamma function of 132.

**GAUSS** calculates the probability that a member of a standard normal population will fall somewhere between the mean and a specific number of standard deviations from the mean. For example, if you want to calculate the probability that somebody’s test results will be less than three standard deviations from the mean, you would run GAUSS(3).

**PERMUTATIONA** calculates the number of permutations including repetitions that are possible for a particular set. To use this function, you need to know the number of objects in the set and the number that will be chosen.

**PHI** determines the phi value, or the value of the density function for a standard normal distribution. For example, PHI (1.43) calculates the density function for a standard normal distribution with a value of 1.43.

**SKEW.P** tells you how much a distribution is skewed based on its population. Usually, you input the data from the population then run SKEW.P on the entire data set.

**Text Functions**

**NUMBERVALUE** changes text to numbers based on locale-independent method. For example, 3.5% will be converted into .035.

**UNICHAR** converts a number into the unicode character associated with that number. For example, UNICHAR(66) returns a value of B.

**UNICODE** is the complement of UNICHAR; it returns the number associated with a particular character. For example, UNICODE(B) returns a value of 66.

**Web Functions**

**ENCODEURL** converts a string of text into URL code so that you can filter results of a database. For example, ENCODEURL(“michael herman”) returns michael%20herman.

**FILTERXML** returns XML data from a specified XML path. You must provide the XML.

**WEBSERVICE** returns data from an online service. You must provide the URL for WEBSERVICE to draw from.