LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 - Math formula

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

   Subtract numbers

   Multiply numbers

   Divide numbers

   Calculate percentages

   Calculate the smallest or largest number in a range

   Calculate the factorial or permutation of a number

   Create a multiplication table

   Round a number

   Raise a number to a power

 

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 .

  1. Click a cell below the column of numbers or to the right of the row of numbers.
  2. 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

 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 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.

  1. Click a cell in the range of cells.
  2. 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.

Install and load the Conditional Sum Wizard add-in program

    1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
    2. Select Excel Add-ins in the Manage list box, and then click Go.
    3. In the Add-Ins available list, select the Conditional Sum Wizard check box, and then click OK.
  1. 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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:

10-5

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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.

  1. In an empty cell, enter the number that you want to multiply by.
  2. Select the cell, and on the Home tab, in the Clipboard group, click Copy .
  3. Select the range of numbers that you want to multiply.
  4. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special.
  5. Under Operation, click Multiply.
  6. Click OK.
  7. 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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 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:

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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.

 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

  1. Select a cell below or to the right of the numbers for which you want to find the smallest number.
  2. 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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 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.).

  1. 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

    1. Press CTRL+C.
    2. In the worksheet, select cell A1, and press CTRL+V.
    3. 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.  

 

1

2

3

A

1

1

=A1*A2

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

How to copy values

    1. 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.).
    2. 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.).
    3. 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

  1. Select the cells that you want to format.
  2. 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

  1. On the Home tab, in the Number group, click the arrow, and then click More.
  2. In the Category list, click Currency, Accounting, Percentage, or Scientific.
  3. 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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

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

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

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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

A

B

Formula

Description (Result)

=POWER(5,2)

Calculates five squared (25)

=5^3

Calculates five cubed (125)

 

                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us