7 Ways to Round in Excel
April 18, 2017
See 7 ways to round numbers in Excel, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.
Have you ever been in a meeting, and had someone point out that your totals are wrong? It looks odd, but sometimes 2+2 = 5 in Excel, and you might be able to guess why.
The problem occurs if you use number formatting to make the numbers look rounded, when they really aren't. Really, the numbers are 2.3 and 2.3, which add up to 4.6.
In some cases, you might want to use a function to round the numbers, instead of just hiding the decimals. I've made a slide show that shows 7 ways to round in Excel, and you can see it, and some rounding examples, on my Excel Rounding Functions page. There are workbooks to download too, so you can follow the examples.
If you do any accounting work, there are regular tasks that need to be done at month end. In one of my workbooks, I have to add a couple of entries on a worksheet, before starting the next month's entries.
Too often, I forgot to do that, and had to go back and enter them later. So, to remind myself, I added a formula at the top of the sheet, with a hyperlink link at appears on the first day of the month. Conditional formatting adds a bright colour too, so it's hard to ignore!
The hyperlink takes me to another sheet, where I can copy the items, and paste them onto the main sheet:
=IF(DAY(TODAY())=1,HYPERLINK("#MonthEnd!A1","Add MONTH END items"),"")
See more HYPERLINK examples on my website.
Thanks for sending your formulas for last week's topic of using SUMIFS with blank criteria cells. You can download my sample file that shows the original formula, my revised formula, and your suggestions. The zipped workbook is in xlsx format, with no macros.
Here are a couple of recent Excel articles that you might find useful.
Troubleshooting - On his Engineering Excel blog, Charlie Young shows two quick ways to troubleshoot broken Excel formulas. He uses an engineering example, but the tips are useful for any formulas. (Level - All)
Power BI - Learn about the pitfalls of Power Query, in this series of articles from Gil Raviv. He previously led the Power Query team at Microsoft, so he's certainly well-qualified! (Level - Intermediate)
The canopy is up, and things are finally sprouting in our garden. I'm especially happy to see the rhubarb, but we'll need to wait a while before the harvest. The sedum and day lilies are back in abundance, and there is a little row of narcissus too. And, of course, the weeds are doing nicely too. ;-)
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
NOTE: If you have trouble with the images or links in this email, use this URL to see the online version: http://www.contextures.com/newsletter/excelnews2017/20170418ctx.html
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: July 21, 2017 9:39 AM