Using Formulas and Functions in Microsoft Excel 2013
- By Curtis Frye
- 4/15/2013
- Creating simple cell formulas
- Assigning names to groups of cells
- Using names in formulas
- Creating a formula that references values in an Excel table
- Creating formulas that reference cells in other workbooks
- Changing links to different workbooks
- Analyzing data by using the Quick Analysis lens
- Summing a group of cells without using a formula
- Creating a summary formula
- Summing with subtotals and grand totals
- Exploring the Excel function library
- Using the IF function
- Checking formula references
- Debugging your formulas
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.
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.
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.
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. |