Microsoft Office Tutorials and References

In Depth Information

**Mastering Excel’s Built-in Functions**

The ROUND function rounds a number to a specified number of digits. If you have a

column full of calculated results and you don’t mind permanently converting them to a

result that is less accurate than the original, use this function. Its syntax is ROUND(number,

num_digits). Two additional functions in the same family allow you to round numbers in a

specific direction: ROUNDDOWN and ROUNDUP use the same syntax and round the value

specified in the number argument down (toward zero) or up (away from zero).

If the details after the decimal point don’t matter, use one of the following functions to

round a number to an integer. INT rounds a number down to the nearest integer, EVEN

rounds a number up to the nearest even integer, and ODD rounds a number up to the

nearest odd integer. All three functions take a single number argument.

And we would be remiss if we left out one of our favorite math functions, which hasn’t had

much use in the past couple millennia but could be poised for a comeback. If you’ve been

baffled by the intricacies of Roman numerals (“When was that movie copyrighted?”), let

Excel help you out. The ROMAN function accepts any Arabic numeral as an argument and

converts it to Roman numerals. So, =ROMAN(2011) correctly returns the result MMXI,

formatted as text. A second, optional argument lets you choose simpler, more concise formats

(MXMIX for 1999 instead of the traditional MCMXCIX, for example), but we prefer the

classic display.

Engineering Functions

More than 40 functions are available in this category, and virtually all of them are useful

only to professional engineers and engineering students. The single noteworthy exception

is the CONVERT function, which allows you to create formulas for translating measurements

between different systems. The function’s syntax is CONVERT(number,from_unit,to_unit).

The second two arguments, which must be enclosed in quotation marks, allow you to

specify the type of conversion. To translate gallons to liters, for example, enter a value

representing the number of gallons in cell A1, and then enter the following formula in cell B1:

=CONVERT(A1,"gal","l")

When you use the formula helper to enter the arguments for this function, the full list

appears for the from_unit argument, with nearly 50 choices available, covering mass,

distance, weight, temperature, and other measurement systems. You can convert meters to

feet, angstroms to inches, or Atomic mass units to grams, if you’re so inclined. After you

choose an option for the from_unit argument (“gal” in this example), the options for the

to_unit argument are filtered to include only those that are appropriate for the

measurement system from which you’re converting, as shown next.