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.
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
Neil,
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.
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)
Thanks for the feedback, Aaron! I’m glad you found this to be useful. 🙂
There’s a much simpler way to do this.
=roundup((C5*0.2),0)*5