How to round number to nearest 5/10/50/100 in Excel?
Sometimes, you may want to convert numbers to nearest 5/10/50/100 for calculation in Excel. This Tutorial provides some simple formulas to round numbers to nearest specific number, and also introduce the formulas to round numbers to next or last nearest one.
Round to nearest 5/10/50/100
|Round to nearest 5||=ROUND(A2/5,0)*5|
|Round to nearest 10||=ROUND(A2/10,0)*10|
|Round to nearest 50||=ROUND(A2/50,0)*50|
|Round to nearest 100||=ROUND(A2/100,0)*100|
You also can use this formula =MROUND(A2,5) to round numbers to nearest 5.
Round to next nearest 5/10/50/100
|Round to next nearest 5||=ROUNDUP(A2/5,0)*5|
|Round to next nearest 10||=ROUNDUP(A2/10,0)*10|
|Round to next nearest 50||=ROUNDUP(A2/50,0)*50|
|Round to next nearest 100||=ROUNDUP(A2/100,0)*100|
Round to last nearest 5/10/50/100
|Round to last nearest 5||=ROUNDDOWN(A2/5,0)*5|
|Round to last nearest 10||=ROUNDDOWN(A2/10,0)*10|
|Round to last nearest 50||=ROUNDDOWN(A2/50,0)*50|
|Round to last nearest 100||=ROUNDDOWN(A2/100,0)*100|
Easily Round numbers up or down or to even in Excel without formula
|As we known, there are some formulas can round numbers, but you do not want to use formula to round numbers, the Round utility of Kutools for Excel can do you a favor. Click for 30 days free trial!|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!