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!

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>