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.

Using Excel

Getting Acquainted with Visual Basic for Applications

Collecting and Cleaning Up Data

Charting

Statistical Analysis

Time Series Analysis

Mathematical Functions

Curve Fitting and Regression

Solving Equations

Numerical Integration and Differentiation

Solving Ordinary Differential Equations

Solving Partial Differential Equations

Performing Optimization Analyses in Excel

Introduction to Financial Calculations

Index

show all menu





Excel Scientific and Engineering Cookbook
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-2017.
If you may any questions please contact us: flylib@qtcs.net