How to always round up to the next 5 in Google Sheets

EDIT: Using the Ceiling function is much simpler as mentioned in the comments of this.

Google Sheets can make an excellent alternative to Microsoft Excel (though I do prefer Excel for certain tasks). While working in it today, I needed a set of numbers to always round up to the next 5, unless the number was already at a 5 or a 0. Here are some examples to show what I needed:

  • $5,100 => $5,100 (no change, because the number was already rounded, ending with a 0)
  • $5,105 => $5,105 (no change, because the number was already rounded, ending with a 5)
  • $5,100.01 => $5,105 (round up to account for the $0.01)
  • $5,104 => $5,105 (round up to account for the $4)
  • $5,106 => $5,110 (round up to account for the $6)

I found all kinds of examples showing how to round to the nearest 5, but never always up to the next 5, unless the number is already at a 5 or 0. So without further adieu, here’s the formula I created:

=if(MOD(C5,5) > 0, 5*ROUNDDOWN(C5/5,0)+5, 5*ROUNDDOWN(C5/5,0))

In this example, the number I was trying to round to the next 5 was “C5.”

Google Sheets rounding example

The second to the last row, which was “C5” in this Sheet, shows $5,100.59. Below that, you see it was correctly rounded up to $5,105.

Of course, using this formula, perhaps with some slight modifications, could work for other similar cases (e.g., rounding to the next 10, etc.), but this solves the “5” problem.

Let me know if I overthought this or if you have a more elegant (and shorter) solution. I’m all for refactoring this to make it more succinct if possible! For now, it works perfectly.

NOTE: I found the “5*ROUNDDOWN(C5/5,0)” section in a comment on a forum somewhere, but somehow I cannot find the link, even after reopening all of the links in my history since starting this. I’d love to give credit where it’s due, but I can’t seem to find where I got this snippet.

Subscribe
Notify of
guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Neil
Neil
2 years ago

The CEILING() function will also make this a very simple formula. This function rounds the value up to the next multiple of a given factor, much like MROUND(), but that function is nearest multiple only. The FLOOR() function is the opposite of CEILING() and will always round down to a multiple.


=CEILING(5100, 5) // Equals: 5100
=CEILING(5105, 5) // Equals: 5105
=CEILING(5100.01, 5) // Equals: 5105
=CEILING(5104, 5) // Equals: 5105
=CEILING(5106, 5) // Equals: 5110

Aaron
Aaron
2 years ago

THANK YOU, I was able to not only use this but adapt it to round up to the nearest 0.25
=if(MOD(D2,0.25) > 0, (25*ROUNDDOWN((D2*100)/25,0)+25)/100, (25*ROUNDDOWN((D2*100)/25,0))/100)