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