Round Values To The Nearest Fraction
Category: Formulas | [Item URL]
Q. Is it possible to round a dollar amount to the nearest 25 cents? For example, if a number appears as $1.65, I would like to convert it to $1.75. Excel's ROUND() function seems to work only with whole numbers.
Yes, you can use Excel's ROUND() function to achieve the rounding you want. The following formula, which assumes that your value is in cell A1, will do the job for you.
=ROUND(A1/.25,0)*.25
The formula divides the original value by .25 and then multiplies the result by .25. You can, of course, use a similar formula to round values to other fractions. For example, to round a dollar amount to the nearest nickel, simply substitute .05 for each of the two occurrences of ".25" in the preceding formula.
Excel Tips
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface of the most recent version, Excel 2007, is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 user interface.
All Tips
Browse Tips by Category
Search for Tips
Tip Books
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks
Contains more than 200 useful tips and tricks for Excel | Other Excel 2003 books | Amazon link: John Walkenbach's Favorite Excel Tips & Tricks