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).
- Round to the nearest 0.05.
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))