Round to the Nearest $0.05 with MROUND

February 23, 2022 - by Bill Jelen

Problem: I know I can use the ROUND function to round to the nearest dollar or penny. How do I round to the nearest nickel or quarter?

Strategy: You can use the MROUND function. This function will round a number to the nearest multiple of the second argument. To round to the nearest nickel, use =MROUND(B2,0.05). To round to the nearest quarter, you use =MROUND(B2,0.25).

Gotcha: Both arguments in the MROUND function must have the same sign. This can be difficult when you have a mixture of positive and negative values. The SIGN function will return either a 1 or -1, based on the sign of a number. If there is a possibility that the first argument might be negative, you can multiply the second argument by SIGN of the first argument. =MROUND(B2,0.05*SIGN(B2))

This article is an excerpt from Power Excel With MrExcel

Title photo by Coline Beulin on Unsplash