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

Add numbers
Excel 2007
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 all contiguous numbers in a row or column
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:
15
Top of Page
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.
Top of Page
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.
How to copy an example

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

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
SUM
Top of Page
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.
How to copy an example

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

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
Top of Page
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.
How to copy an example

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

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
SUM
IF
Top of Page
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.
How to copy an example

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

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
Top of Page
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 addin (addin: 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 addin program.
Install and load the Conditional Sum Wizard addin program


Click the Microsoft Office Button , click Excel Options, and then click the Addins category.

Select Excel Addins in the Manage list box, and then click Go.

In the AddIns 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.
Top of Page
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.
How to copy an example

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

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
SUM
IF
Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Subtract numbers
Excel 2007
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 cell
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:
105
The cell displays the following result:
5
Top of Page
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.
How to copy an example

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)

=A2A3

Subtracts 9,000 from 15,000 (6,000)

=SUM(A2:A4)

Adds all numbers in the list, including negative numbers (16,000)


Function details
SUM
Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Multiply numbers
Excel 2007
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 numbers in a cell
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:
50
Top of Page
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.
Top of Page
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.
How to copy an example

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
Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Divide numbers
Excel 2007
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 crosscountry 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:
2
Top of Page
Divide cells in a range
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

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)


Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Calculate percentages
Excel 2007
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 amount if you know the total and percentage
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.
How to copy an example

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.
Top of Page
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.
How to copy an example

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 .
Top of Page
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.
How to copy an example

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)


Top of Page
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.
How to copy an example

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)

=(B2A2)/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
Top of Page
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.
How to copy an example

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*(1B2)

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.
Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Calculate the smallest or largest number in a range
Excel 2007
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 in a contiguous row or column
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.
Top of Page
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.
How to copy an example

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

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
LARGE
MAX
MIN
SMALL
Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Calculate the factorial or permutation of a number
Excel 2007
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.
How to copy an example

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 6*5*4*3*2*1 (720)

=FACT(3)

Factorial of 3, or 3*2*1 (6)

=FACT(A2+4)

Factorial of 10 (3628800)


Function details
FACT
Excel > Formula and name basics > Examples of formulas > Math
Create a multiplication table
Excel 2007
Let's say you want to help your daughter better understand math concepts by dynamically creating a multiplication table. You can use a twovariable 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: oneinput tables and twoinput 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.
How to copy an example


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.


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 WhatIf 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: oneinput tables and twoinput tables.).

Click Copy , and then select the upperleft 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
Excel 2007
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?
Change the number of decimal places displayed, without changing the number
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 builtin 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.
Top of Page
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.
How to copy an example

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
ROUNDUP
EVEN
ODD
Top of Page
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.
How to copy an example

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
Top of Page
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.
How to copy an example

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
Top of Page
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.
How to copy an example

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
Top of Page
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.
How to copy an example

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,3LEN(INT(A2)))

Rounds the top number to 3 significant digits (5490000)

=ROUNDDOWN(A3,3LEN(INT(A3)))

Rounds the bottom number down to 3 significant digits (22200)

=ROUNDUP(A2,5LEN(INT(A2)))

Rounds the top number up to 5 significant digits (5492900)


Function details
ROUNDUP
ROUNDDOWN
ROUND
LEN, LENB
INT
Top of Page
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.
How to copy an example

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
Top of Page
Excel > Formula and name basics > Examples of formulas > Math
Raise a number to a power
Excel 2007
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.
How to copy an example

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)

=POWER(5,2)

Calculates five squared (25)

=5^3

Calculates five cubed (125)

