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

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.