Posted by Jerritte Couture and filed under Tips & Tricks

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.



About Jerritte Couture

A graduate of the Wright State University Department of Psychology’s Honors Program, Jerritte's passion resides in being a user advocate. He focuses much of his attention on creating user-friendly interfaces with the Edge Webware design/development team.

Jerritte is the Founder and President of Edge Webware. He counts himself very blessed to work with such a wonderful team of designers and developers at Edge!

5 Responses to “How to always round up to the next 5 in Google Sheets”

  1. 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

    • Jerritte Couture


      Now I’m wondering why I wasn’t able to find this back in 2016 when I originally wrote this post! 🙂 I wonder if I either just missed this somehow in all my searches or whether this was added to Sheets at a later time.

      In either case, thanks for the additional info. This is a far simpler solution.

  2. 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)

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>