Excel 2007 - Math formula

Calculate the smallest or largest number in a range

Tips

Members

Utility for Excel:

- Compare worksheets
- Database analysis
- Stock to your Excel
- Password recovery
- and many more...

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

Calculate percentages

Calculate the factorial or permutation of a number

Create a multiplication table

Round a number

Raise a number to a power

Add numbers

Add all contiguous numbers in a row or column

Let's say you want to sum prices for all items not on sale in a store or sum gross profit margins for all departments under budget. There are several ways to add numbers.

What do you want to do?

Add numbers in a cell

Add noncontiguous numbers

Add numbers based on one condition

Add numbers based on multiple conditions

Add numbers based on criteria stored in a separate range

Add numbers based on multiple conditions with the Conditional Sum Wizard

Add unique values

Add numbers in a cell

To do this task, use the + (plus sign) arithmetic operator.

For example, if you type the following formula in a cell:

=5+10

The cell displays the following result:

Add all contiguous numbers in a row or column

To do this task, use AutoSum .

Click a cell below the column of numbers or to the right of the row of numbers.
On the Home tab, in the Editing group, click AutoSum , and then press ENTER.

Add noncontiguous numbers

To do this task, use the SUM function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7

A	B
Salesperson	Invoice
Buchanan	15,000
Buchanan	9,000
Suyama	8,000
Suyama	20,000
Buchanan	5,000
Dodsworth	22,500
Formula	Description (Result)
=SUM(B2:B3, B5)	Adds two invoices from Buchanan, and one from Suyama (44,000)
=SUM(B2,B5,B7)	Adds individual invoices from Buchanan, Suyama, and Dodsworth (57,500)

Note The SUM function can include up to 30 cell or range references.

Function details

Add numbers based on one condition

You can use the SUMIF function to create a total value for one range based on a value in another range, as in the following example.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7

A	B
Salesperson	Invoice
Buchanan	15,000
Buchanan	9,000
Suyama	8,000
Suyama	20,000
Buchanan	5,000
Dodsworth	22,500
Formula	Description (Result)
=SUMIF(A2:A7,"Buchanan",B2:B7)	Sum of invoices for Buchanan (29000)
=SUMIF(B2:B7,">=9000",B2:B7)	Sum of large invoices greater than or equal to 9,000 (66500)
=SUMIF(B2:B7,"<9000",B2:B7)	Sum of small invoices less than 9,000 (13000)

The SUMIF function uses the following arguments

Formula with SUMIF function

Range to evaluate: Check these cells to determine whether a row meets your criteria.

Criteria: The condition that the cells you evaluate must meet for the row to be included in the sum.

Range to sum: Add the numbers in these cells provided that the row satisfies the condition.

Function details

SUMIF

Add numbers based on multiple conditions

To do this task, use the IF and SUM functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10
11

A	B	C	D
Region	Salesperson	Type	Sales
South	Buchanan	Beverages	3571
West	Davolio	Dairy	3338
East	Suyama	Beverages	5122
North	Suyama	Dairy	6239
South	Dodsworth	Produce	8677
South	Davolio	Meat	450
South	Davolio	Meat	7673
East	Suyama	Produce	664
North	Davolio	Produce	1500
South	Dodsworth	Meat	6596
Formula	Description (Result)
=SUM(IF((A2:A11="South")*(C2:C11="Meat"),D2:D11))	Sum of Meat sales in the South region (14719)
=SUM(IF((A2:A11="South")+(A2:A11="East"),D2:D11))	Sum of sales where the region is South or East (32753)

Note The formulas in the example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After copying the example to a blank worksheet, select the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Function details

Add numbers based on criteria stored in a separate range

To do this task, use the DSUM function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10
11

A	B	C	D
Region	Salesperson	Type	Sales
South	Buchanan	Beverages	3571
West	Davolio	Dairy	3338
East	Suyama	Beverages	5122
North	Suyama	Dairy	6239
South	Dodsworth	Produce	8677
South	Davolio	Meat	450
South	Davolio	Meat	7673
East	Suyama	Produce	664
North	Davolio	Produce	1500
South	Dodsworth	Meat	6596
Region	Salesperson	Type	Sales
South		Meat
		Produce
Formula	Description (Result)
=DSUM(A1:D11, "Sales", A12:D13)	Sum of Meat sales in the South region (14719)
=DSUM(A1:D11, "Sales", A12:D14)	Sum of Meat and Produce sales in the South region (25560)

The DSUM function uses the following arguments.

Range to evaluate: The list from which you want to sum.

Field: The label of the column to sum.

Criteria: The range of cells that contains the conditions.

Function details

DSUM

Install and load the Conditional Sum Wizard add-in program

Add numbers based on multiple conditions with the Conditional Sum Wizard

If you want to summarize a range of cells based on specific conditions, you can use the Conditional Sum Wizard. For example, if your range of cells contain sales amounts for different salespeople, the Conditional Sum Wizard add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) program can help you create a formula that calculates the total sales amount for one salesperson.

Click a cell in the range of cells.
On the Formulas tab, in the Solutions group, click Conditional Sum.

If the Conditional Sum command or Solutions tab are not available, then you need to load the Conditional Sum Wizard add-in program.

Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
Select Excel Add-ins in the Manage list box, and then click Go.
In the Add-Ins available list, select the Conditional Sum Wizard check box, and then click OK.

Follow the instructions in the wizard.

Note The formulas created by the Conditional Sum Wizard are array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After you edit these formulas, you must press CTRL+SHIFT+ENTER to lock in the formulas.

Add unique values

To do this task, use the SUM, IF, and FREQUENCY functions.

The following example uses the:

FREQUENCY function to identify the unique values. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a 0 (zero).
IF function to assign a value of 1 to each true condition.
The SUM function to add the unique values.

Tip To see a function evaluated step by step, select the cell containing the formula, and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7
8
9
10

A
Data
986
456
67
1
34
689
456
56
67
Formula	Description (Result)
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,A2:A10))	Add the unique values in cells A2:A10 (2289)

Function details

FREQUENCY

Excel > Formula and name basics > Examples of formulas > Math

Subtract numbers

Subtract numbers in a cell

Let's say you want to find out how many inventory items are not profitable (total inventory – profitable items) or how many employees are approaching retirement age (total employees – employees under 55). There are several ways to subtract numbers.

What do you want to do?

Subtract numbers in a range

Subtract numbers in a cell

To do this task, use the - (minus sign) arithmetic operator.

For example, if you type the following formula in a cell:

10-5

The cell displays the following result:

Subtract numbers in a range

To do this task, use the SUM function. Adding a negative number is the same as subtracting.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
15,000
9,000
-8,000
Formula	Description (Result)
=A2-A3	Subtracts 9,000 from 15,000 (6,000)
=SUM(A2:A4)	Adds all numbers in the list, including negative numbers (16,000)

Function details

Excel > Formula and name basics > Examples of formulas > Math

Multiply numbers

Multiply numbers in a cell

Let's say you want to figure out how much bottled water that you need for a customer conference (total attendees ׳ 4 days ׳ 3 bottles per day) or the reimbursement travel cost for a business trip (total miles ׳ .46). There are several ways to multiply numbers.

What do you want to do?

Multiply a range of numbers by a number

Multiply numbers in different cells by using a formula

Multiply numbers in a cell

To do this task, use the * (asterisk) arithmetic operator.

For example, if you type the following formula in a cell:

=5*10

The cell displays the following result:

Multiply a range of numbers by a number

Important This procedure replaces the numbers in the range with the result of the multiplication.

In an empty cell, enter the number that you want to multiply by.
Select the cell, and on the Home tab, in the Clipboard group, click Copy .
Select the range of numbers that you want to multiply.
On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.

Multiply numbers in different cells by using a formula

To do this task, use the asterisk (*) operator or the PRODUCT function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
5
15
30
Formula	Description (Result)
=A2*A3	Multiplies the numbers in the first two cells (75)
=PRODUCT(A2:A4)	Multiplies all the numbers in the range (2250)
=PRODUCT(A2:A4,2)	Multiplies all the numbers in the range, and 2 (4500)

Function details

PRODUCT

Excel > Formula and name basics > Examples of formulas > Math

Divide numbers

Let's say you want to find out how many person hours it took to finish a project (total project hours ק total people on project) or the actual miles per gallon rate for your recent cross-country trip (total miles ק total gallons). There are several ways divide numbers.

What do you want to do?

Divide numbers in a cell

Divide cells in a range

Divide numbers in a cell

To do this task, use the / (forward slash) arithmetic operator.

For example, if you type the following formula in a cell:

=10/5

The cell displays the following result:

Divide cells in a range

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
15,000
12
Formula	Description (Result)
=A2/A3	Divides 15,000 by 12 (1250)

Excel > Formula and name basics > Examples of formulas > Math

Calculate percentages

Calculate the amount if you know the total and percentage

Let's say you want to calculate a sales tax for different states, compute a grade for a test score, or determine a percent change in sales between two fiscal quarters. There are several ways to calculate percentages.

Percentages are calculated by using the following equation:

amount/total = percentage

Where percentage is in decimal format.

What do you want to do?

Calculate the percentage if you know the total and amount

Calculate the total if you know the amount and percentage

Calculate the difference between two numbers as a percentage

Increase or decrease a number by a percentage

Calculate the amount if you know the total and percentage

For example, if you purchase a computer for $800 and there is an 8.9% sales tax, how much do you have to pay for the sales tax? In this example, you want to find 8.9% of 800.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A	B
Purchase price	Sales tax (in decimal form)
800	0.089
Formula	Description (Result)
=A2*B2	Multiplies 800 by 0.089 to find the amount of sales tax to pay ($71.20)

Note To convert a number in percentage format to a decimal, divide it by 100. For example, the sales tax in this example (8.9) divided by 100 is .089.

Calculate the percentage if you know the total and amount

For example, if you score 42 points correctly out of 50, what is the percentage of correct answers?

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A	B
Points answered correctly	Total points possible
42	50
Formula	Description (Result)
=A2/B2	Divides 42 by 50 to find the percentage of correct answers (0.84 or 84%)

Note You can view the number as a percentage. Select the cell, and then on the Home tab, in the Number group, click Percent Style .

Calculate the total if you know the amount and percentage

For example, the sale price of a shirt is $15, which is 25% off the original price. What is the original price? In this example, you want to find 75% of which number equals 15.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A	B
Sale price	100% minus the discount (in decimal form)
15	0.75
Formula	Description (Result)
=A2/B2	Divides 15 by 0.75 to find the original price (20)

Calculate the difference between two numbers as a percentage

For example, your earnings are $2,342 in November and $2,500 in December. What is the percentage change in your earnings between these two months? To do this task, use the ABS function and the subtraction (-) and division (/) operators.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A	B
November earnings	December earnings
2342	2500
Formula	Description (Result)
=(B2-A2)/ABS(A2)	Divides the difference between the second and first numbers by the absolute value of the first number to get the percentage change (0.06746 or 6.75%)

Note You can view the number as a percentage. Select the cell, and then on the Home tab, in the Number group, click Percent Style .

Function details

ABS

Increase or decrease a number by a percentage

For example, you spend an average of $25 on food each week, and you want to cut your weekly food expenditures by 25%. How much can you spend? Or, if you want to increase your weekly food allowance of $25 by 25%, what is your new weekly allowance?

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A	B
Number	Percentage
25	25%
Formula	Description (Result)
=A2*(1-B2)	Decreases 25 by 25% (18.75)
=A2*(1+B2)	Increases 25 by 25% (31.75)
=A2*(1+35%)	Increases 25 by 35% (33.75)

Note When you type a number followed by a percent sign (%), the number is interpreted as a hundredth of its value. For example, 5% is interpreted as .05.

Excel > Formula and name basics > Examples of formulas > Math

Calculate the smallest or largest number in a range

If the cells are in a contiguous row or column

Let's say you want to find out who has the the smallest error rate in a production run at a factory or the largest salary in your department. There are several ways to calculate the smallest or largest number in a range.

What do you want to do?

If the cells are not in a contiguous row or column

If the cells are in a contiguous row or column

Select a cell below or to the right of the numbers for which you want to find the smallest number.
On the Home tab, in the Editing group, click the arrow next to AutoSum , click Min (calculates the smallest) or Max (calculates the largest), and then press ENTER.

If the cells are not in a contiguous row or column

To do this task, use the MIN, MAX, SMALL, or LARGE functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.


1
2
3
4
5
6
7

A
Data
10
7
9
27
0
4
Formula	Description (Result)
=MIN(A2:A7)	Smallest number in the range (0)
=MAX(A2:A7)	Largest number in the range (27)
=SMALL(A2:A7, 2)	Second smallest number in the range (4)
=LARGE(A2:A7,3)	Third largest number in the range (9)

Function details

Excel > Formula and name basics > Examples of formulas > Math

Calculate the factorial or permutation of a number

Let's say you have six bells, each with a different tone, and you want to find the number of unique sequences in which each bell can be rung once. In this example, you are calculating the factorial of six. In general, use a factorial to count the number of ways in which a group of distinct items can be arranged (also called permutations). To calculate the factorial of a number, use the FACT function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
6
Formula	Description (Result)
=FACT(A2)	Factorial of 6, or 65432*1 (720)
=FACT(3)	Factorial of 3, or 321 (6)
=FACT(A2+4)	Factorial of 10 (3628800)

Function details

FACT

Excel > Formula and name basics > Examples of formulas > Math

Create a multiplication table

Let's say you want to help your daughter better understand math concepts by dynamically creating a multiplication table. You can use a two-variable data table (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.).

Set up a worksheet with the following structure.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

=A1*A2

Enter a row of values from B3 to the right, for example, 1 through 10.
Enter a column of values from A4 down, for example, 1 through 10.
Select all cells in the range except cells A1 and A2.
On the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table.
In the Row input cell box, enter A1.
In the Column input cell box, enter A2.
Click OK.
Optionally, you can freeze the data table by converting the result to their values.

How to copy values

Select all resulting values in the data table (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.).
Click Copy , and then select the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.).
Click the arrow next to Paste , and then click Values. (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.)

Excel > Formula and name basics > Examples of formulas > Math

Round a number

Change the number of decimal places displayed, without changing the number

Let's say you want to round a number to the nearest whole number because decimal values are not significant to you or round a number to multiples of 10 to simplify an approximation of amounts. There are several ways to round a number.

What do you want to do?

Round a number up

Round a number down

Round a number to the nearest number

Round a number to a near fraction

Round a number to a significant digit above 0

Round a number to a specified multiple

Change the number of decimal places displayed, without changing the number

On a worksheet

Select the cells that you want to format.
To display more or fewer digits after the decimal point, on the Home tab, in the Number group, click Increase Decimal or Decrease Decimal .

In a built-in number format

On the Home tab, in the Number group, click the arrow, and then click More.
In the Category list, click Currency, Accounting, Percentage, or Scientific.
In the Decimal places box, enter the number of decimal places that you want to display.

Round a number up

To do this task, use the ROUNDUP, EVEN, or ODD functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
20.3
-5.9
12.5493
Formula	Description (Result)
=ROUNDUP(A2,0)	Rounds 20.3 up to the nearest whole number (21)
=ROUNDUP(A3,0)	Rounds -5.9 up (-6)
=ROUNDUP(A4,2)	Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)
=EVEN(A2)	Rounds 20.3 up to the nearest even number (22)
=ODD(A2)	Rounds 20.3 up to the nearest odd number (21)

Function details

Round a number down

To do this task, use the ROUNDDOWN function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
20.3
-5.9
12.5493
Formula	Description (Result)
=ROUNDDOWN(A2,0)	Rounds 20.3 down to the nearest whole number ( 20)
=ROUNDDOWN(A3,0)	Rounds -5.9 down (-5)
=ROUNDDOWN(A4,2)	Rounds the number down to the nearest hundredth, two decimal places ( 12.54)

Functions details

ROUNDDOWN

Round a number to the nearest number

To do this task, use the ROUND function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
20.3
5.9
-5.9
Formula	Description (Result)
=ROUND(A2,0)	Rounds 20.3 down, because the fractional part is less than .5 (20)
=ROUND(A3,0)	Rounds 5.9 up, because the fractional part is greater than .5 (6)
=ROUND(A4,0)	Rounds -5.9 down, because the fractional part is less than -.5 (-6)

Function details

ROUND

Round a number to a near fraction

To do this task, use the ROUND function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
1.25
30.452
Formula	Description (Result)
=ROUND(A2,1)	Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)
=ROUND(A3,2)	Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)

Functions details

ROUND

Round a number to a significant digit above 0

To do this task, use the ROUND, ROUNDUP, ROUNDDOWN, LEN, and INT functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A
Data
5492820
22230
Formula	Description (Result)
=ROUND(A2,3-LEN(INT(A2)))	Rounds the top number to 3 significant digits (5490000)
=ROUNDDOWN(A3,3-LEN(INT(A3)))	Rounds the bottom number down to 3 significant digits (22200)
=ROUNDUP(A2,5-LEN(INT(A2)))	Rounds the top number up to 5 significant digits (5492900)

Function details

Round a number to a specified multiple

To do this task, use the MROUND function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Create a blank workbook or worksheet.
Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

A	B
Formula	Description (Result)
=MROUND(16, 5)	Rounds 16 to a nearest multiple of 5 (15)
=MROUND(-16, -5)	Rounds -16 to a nearest multiple of -5 (-15)
=MROUND(2.6, 0.08)	Rounds 2.6 to a nearest multiple of 0.08 (2.64)
=MROUND(5, -2)	Returns an error, because 5 and -2 have different signs (#NUM!)

Function details

MROUND

Excel > Formula and name basics > Examples of formulas > Math

Raise a number to a power

Let's say you want to calculate an extremely small tolerance level for a machined part or the vast distance between two galaxies. To raise a number to a power, use the "^" operator or the POWER function.

Example

The example may be easier to understand if you copy it to a blank worksheet.