# Rounding and Truncating Numbers

Problem

Your calculations call for you to either round or truncate values in your spreadsheet.

Solution

Use one or more of the built-in functions shown in Table 7-6.

Table 7-6. Excel functions for rounding and truncating

Function

Syntax

Description

ROUND

=ROUND(n, digits)

Rounds the number n to the specified number of digits

ROUNDUP

=ROUNDUP(n, digits)

Rounds the number n up to the specified number of digits

ROUNDDOWN

=ROUNDDOWN(n, digits)

Rounds the number n down to the specified number of digits

MROUND

=MROUND(n, multiple)

Rounds the number n to the nearest multiple

CEILING

=CEILING(n, multiple)

Rounds the number n up to the nearest multiple

EVEN

=EVEN(n)

Rounds the number n to the nearest even integer

ODD

=ODD(n)

Rounds the number n to the nearest odd integer

INT

=INT(n)

Rounds the number n to the nearest integer

trUNC

=trUNC(n, digits)

Truncates the number n at the specified number of digits

Discussion

In Excel you can format any cell displaying a value to display that value to a specified number of decimal places. For example, you can format a cell containing the value 4.5837450 to display the number to only two decimal places (that is, 4.58). Formatting a cell to display only a certain number of decimal places does not change the underlying value; it only changes what's shown in the cell. To actually change the value, you need to use the rounding or truncating functions shown in Table 7-6.

I pretty much use the ROUND function as my workhorse for rounding numbers. For example, to round the value 3.213 to one decimal place use =ROUND(3.213, 1); this returns 3.2. To round a value to the nearest integer, specify 0 for the number of digits. For example, to round 3.213 to the nearest integer use =ROUND(3.213, 0) this returns 3.

You can also use ROUND to round a value to the nearest significant digit to the left of the decimal place. To do so, specify a negative number of digits. For example, =ROUND(121.986,-1) returns a value of 120. The formula =ROUND(12839.31,-3) returns a value of 13,000. Excel Scientific and Engineering Cookbook (Cookbooks (OReilly))
ISBN: 0596008791
EAN: 2147483647
Year: N/A
Pages: 206
Authors: David M Bourg

Similar book on Amazon

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net 