Math formulas for microsoft excel

Note that some of the Excel math functions listed below were introduced in recent versions of Excel, and so are not available in earlier versions. Excel Functions. Basic Numeric Information.

Performs a specified calculation e. Rounding Functions. Rounds a number away from zero i. Rounds a number up , regardless of the sign of the number, to a multiple of significance New in Excel Rounds a number up , regardless of the sign of the number, to a multiple of significance. New in Excel Rounds a number up to the nearest integer or to the nearest multiple of significance New in Excel Rounds a number towards zero , i. Rounds a number down , regardless of the sign of the number, to a multiple of significance New in Excel Rounds a number down, to the nearest integer or to the nearest multiple of significance New in Excel Making a reference to a cell or a range of cells on another worksheet in the same workbook.

The difference between absolute, relative and mixed references. Relative references A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to.

If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references.

Mixed references A mixed reference has either an absolute column and relative row, or absolute row and relative column. If the position of the cell that contains the formula changes, the relative reference is changed, and the absolute reference does not change. If you copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. Conveniently referencing multiple worksheets If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a 3-D reference.

A 3-D reference includes the cell or range reference, preceded by a range of worksheet names. Excel uses any worksheets stored between the starting and ending names of the reference. B5 adds all the values contained in cell B5 on all the worksheets between and including Sheet 2 and Sheet P, VAR. What occurs when you move, copy, insert, or delete worksheets The following examples explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D reference.

A2:A5 to add cells A2 through A5 on worksheets 2 through 6. Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 the endpoints in this example , Excel includes all values in cells A2 through A5 from the added sheets in the calculations.

Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from the calculation. Move If you move sheets from between Sheet2 and Sheet6 to a location outside the referenced sheet range, Excel removes their values from the calculation.

Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook, Excel adjusts the calculation to accommodate the new range of sheets between them. Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to accommodate the range of sheets between them. You can also use a reference style where both the rows and the columns on the worksheet are numbered.

The R1C1 reference style is useful for computing row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number. When you record a macro, Excel records some commands by using the R1C1 reference style.

For example, if you record a command, such as clicking the AutoSum button to insert a formula that adds a range of cells, Excel records the formula by using R1C1 style, not A1 style, references. You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style check box under the Working with formulas section in the Formulas category of the Options dialog box.

To display this dialog box, click the File tab. You can always ask an expert in the Excel Tech Community or get support in the Answers community. Excel functions alphabetical. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon.

Pictures helped. Didn't match my screen. Incorrect instructions. Too technical. Not enough information. Not enough pictures. Any additional feedback? Submit feedback. Thank you for your feedback! Converts a number into a text representation with the given radix base. Rounds a number to the nearest integer or to the nearest multiple of significance. Rounds a number up, to the nearest integer or to the nearest multiple of significance.

Returns the number of combinations with repetitions for a given number of items. Converts a text representation of a number in a given base into a decimal number. Returns e raised to the power of a given number.

Rounds a number down, to the nearest integer or to the nearest multiple of significance. Returns the sum of the difference of squares of corresponding values in two arrays.