Surprise, Surprise! Excel Can Handle Fractions

SAN FRANCISCO (03/20/2000) - Some types of data, such as stock market quotes, normally display as fractions, not decimals. To enter a fraction in Microsoft Corp.'s Excel, type the whole number (or integer) followed by a space, and then type the fraction, using a slash (for example, 5/8). If you type only a fraction, Excel may interpret it as a date (so it might read 5/8 as May 8). To avoid this mistranslation, enter 0, a space, and then the fraction.

When you enter a fractional value, Excel automatically applies a fraction number format that reduces it to the smallest possible denominator. For example, if you enter 16 2/8, Excel displays the number as 16 1/4. In some cases, however, you'll want the fractions to use a common denominator. For example, you might want the value 16 2/8 to be shown as 16 4/16. To obtain this result, select your cells and choose Format*Cells. Then select the Number tab and choose Fraction from the Category list. Finally, select the desired number format from the Type list.

You can also express fractional data using a decimal point. For instance, the number 9 4/16 could appear as 9.04. Here, the digits to the right of the decimal represent 16ths. To display values in this format, use Excel's DOLLARFR() function. It's available only when the Analysis ToolPak is installed (select Tools*Add-ins to install it). The DOLLARFR() function takes two arguments: the number and an integer for the denominator. The formula =DOLLARFR(9.25,16), for example, returns 9.04.

This function is also useful for nondollar data. So, if you work with feet and inches, you can represent 11.5 feet as 11.06 (11 feet, 6 inches) by using the formula =DOLLARFR(11.5,12). The value will then appear as '11 [feet] 6 [inches]'.

The DOLLARFR() function is for display only. You can't use the value it returns in other calculations or in charts. To perform calculations on such values, reconvert them into decimal values by using the DOLLARDE() function (also part of the Analysis ToolPak).

Unlink a Chart Series from its Data RangeNormally, an excel chart stores data in a range. If you change the data in that range, the chart updates automatically. Sometimes you may want to unlink the chart from its data ranges and produce a static chart that remains unaffected by later changes in the data. For example, suppose you plot data generated by various what-if scenarios, and you want to save a chart that represents some baseline scenario for comparison with others.

One way to create a static chart is to copy and paste it as a picture. Activate your chart, hold down the key, and choose Edit*Copy Picture (this option is available only when you hold down as you select Edit). The Copy Picture dialog box will appear. Click OK to accept the defaults. Then click anywhere in your worksheet and choose Edit*Paste.

Another way to create a static chart is to convert the range references into arrays. Select a chart series and then click the formula bar to activate the SERIES() formula. Press to convert the range references into arrays.

Repeat this for each series in the chart. Now the chart remains formattable rather than becoming a picture. The formula bar in FIGURE 2 shows the SERIES() formula after conversion of the range references into arrays.

Send questions and tips to john@j-walk.com. We pay $50 for published items.

Contributing Editor John Walkenbach is the author of Excel 2000 Formulas (IDG Books, 2000) and maintains The Spreadsheet Page (www.j-walk.com).

Avoid Error Displays

Sometimes a formula may return an error message. Usually, you'll want to know when a formula error occurs. But now and then you may prefer to avoid the messages. You can do so by using an IF() function to check for an error. For example, the formula =IF(ISERROR(A1/B1),"",A1/B1) displays a blank if the division results in an error. You can adapt this technique to any operation.

The original formula serves as the argument for the ISERROR() function, and it repeats as the last argument of the IF() function.

Join the newsletter!

Or
Error: Please check your email address.

More about INSMicrosoft

Show Comments