Using Formulas and Functions in Microsoft Excel 2013

4/15/2013

Contents

Debugging your formulas

When you share a workbook with your colleagues, some of the values in that workbook might change rapidly as new data is entered. For example, workbook data probably will change quickly if you are evaluating stock prices. Stock market values change frequently, so your data will as well. You can monitor the value in a cell even while you’re using another workbook by setting a watch. When you set a watch, the values of the cells you’re monitoring appear in the Watch Window.

Another way that you can monitor your data is to check the result of part of a calculation by using the Evaluate Formula dialog box. When you click the Evaluate Formula button, Excel displays the formula in the active cell and the subtotal for part of the calculation. You can move through the formula bit by bit, with Excel showing you the result of each piece of the formula.

Monitor a formula for changes

Click the Formulas tab.
Click Watch Window.
Click Add Watch.
Select the cells that you want to watch.
Click Add.
Click Watch Window.

Click to view larger image

Delete a watch

Click the Formulas tab.
Click Watch Window.
Click the watch that you want to delete.
Click Delete Watch.
Click the Close button.

Click to view larger image

Evaluate parts of a formula

Click the cell with the formula that you want to evaluate.
Click the Formulas tab.
Click Evaluate Formula.
Click Evaluate (one or more times) to move through the formula’s elements.
Click Close.

Click to view larger image

Table 6-1. New functions in Excel 2013

Category	Function Name	Description
Date and Time	DAYS	Calculates the number of days between two dates.
Date and Time	ISOWEEKNUM	Returns the number of the ISO week number of the year for a given date.
Engineering	BITAND	Returns a bitwise AND of two numbers.
Engineering	BITLSHIFT	Returns a numbered generated by shifting the bits in a number to the left by a specified number of places.
Engineering	BITOR	Returns a bitwise OR of two numbers.
Engineering	BITRSHIFT	Returns a number generated by shifting the bits in a number to the right by a specified number of places.
Engineering	BITXOR	Returns a bitwise XOR of two numbers.
Engineering	IMCOSH	Returns the hyperbolic cosine of a complex number.
Engineering	IMCOT	Returns the cotangent of a complex number.
Engineering	IMCSC	Returns the cosecant of a complex number.
Engineering	IMCSCH	Returns the hyperbolic cosecant of a complex number.
Engineering	IMSEC	Returns the secant of a complex number.
Engineering	IMSECH	Returns the hyperbolic secant of a complex number.
Engineering	IMSINH	Returns the hyperbolic sine of a complex number.
Engineering	IMTAN	Returns the tangent of a complex number.
Financial	PDURATION	Calculates the number of periods that an investment will take to reach a value.
Financial	RRI	Calculates the equivalent interest rate for the growth of an investment.
Information	ISFORMULA	Returns TRUE if a cell contains a formula.
Information	SHEET	Returns the sheet number of a sheet.
Information	SHEETS	Returns the number of sheets in a reference.
Logical	IFNA	Returns a value that you specify if an expression results in an #N/A error; otherwise, it returns the value of the expression.
Logical	XOR	Returns the logical exclusive OR of all arguments (that is, it returns TRUE if exactly one argument is true).
Lookup and Reference	FORMULATEXT	Returns the formula in the referenced cell as text.
Math and Trigonometry	ACOT	Returns the arccotangent of a number.
Math and Trigonometry	ACOTH	Returns the hyperbolic arccotangent of a number.
Math and Trigonometry	ARABIC	Converts a number written as a Roman numeral into an Arabic number.
Math and Trigonometry	BASE	Converts a number into a text representation of the number in a given base.
Math and Trigonometry	CEILING.MATH	Rounds a number up to the nearest integer or to the nearest significant multiple.
Math and Trigonometry	COT	Returns the hyperbolic cosine of a number.
Math and Trigonometry	COTH	Returns the cotangent of an angle.
Math and Trigonometry	CSC	Returns the cosecant of an angle.
Math and Trigonometry	CSCH	Returns the hyperbolic cosecant of an angle.
Math and Trigonometry	DECIMAL	Converts a text representation of a number in a given base to a decimal number.
Math and Trigonometry	FLOOR.MATH	Rounds a number down to the nearest integer or to the nearest significant multiple.
Math and Trigonometry	ISO.CEILING	Returns a number that is rounded up to the nearest integer or to the nearest significant multiple.
Math and Trigonometry	MUNIT	Returns the unit matrix of the specified dimension.
Math and Trigonometry	SEC	Returns the secant of an angle.
Math and Trigonometry	SECH	Returns the hyperbolic secant of an angle.
Statistical	BINOM.DIST.RANGE	Calculates the probability of a result using the binomial distribution.
Statistical	COMBINA	Returns the number of combinations for a given number of items, allowing replacement.
Statistical	GAMMA	Returns the Gamma function value.
Statistical	GAUSS	Returns 0.5 less than the standard normal cumulative distribution.
Statistical	PERMUTATIONA	Returns the number of permutations for a given number of items, allowing replacement.
Statistical	PHI	Returns the value of the density function for a standard normal distribution.
Statistical	SKEW.P	Returns the skewness of a distribution based on the population instead of a sample from the population.
Text	NUMBERVALUE	Converts text to a number.
Text	UNICHAR	Returns the Unicode character referenced by a number.
Text	UNICODE	Returns the Unicode number of the first character of a text string.
Web	ENCODEURL	Returns a URL-encoded string.
Web	FILTERXML	Returns specific data from XML content using a user-supplied xpath value.
Web	WEBSERVICE	Returns data from a web service.