Excel User Tips


Return to The Spreadsheet Page

Excel page

User tips

 - Charts

 - Formula

 - Formatting

 - Printing

 - Miscellaneous

Help resources


 

Round values to the nearest fraction

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.