# Recipe7.7.Getting to the Root of Things

### Recipe 7.7. Getting to the Root of Things

#### Problem

You need to compute the square root of an expression. Moreover, you'd like to know how to compute other roots, like the cube root, of an expression.

#### Solution

Use the SQRT function for taking square roots and use the POWER function or the ^ operator to handle other roots.

#### Discussion

The syntax for the square root function is simply =SQRT( n ) , where n is a number or an expression containing cell references that evaluates to a number. SQRT returns the positive square root of n .

Excel does not provide other specific functions for taking cube roots and so on; however, you can take such roots using the POWER function or the ^ operator, which performs the same function as the POWER function. To take the cube root of a number, use =POWER( n , 1/3) or n ^(1/3) . Taking the cube root of a number is the same as raising the number to the power 1/3. So for any other roots you may find in your calculations, you can raise a number to the reciprocal of the desired root using POWER or ^ .

See Recipe 7.4 for more information related to the POWER function. Also, take a look at Recipe 1.9 for more on ^ and other operators.

### Recipe 7.8. 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.