Last night, I delivered a presentation on Advanced Excel Tips and Tricks to the Akron & Canton chapters of the Institute of Manageral Accountants. This was a great group of very fluent Excel users. Here is some of the Q&A from that session.
If I enter a formula to take one-third of a number like 100,000 - how can I round that result to get just the whole number portion, or even just the thousands? I know I can format it to hide the decimals, but I don't want the decimals to enter into subsequent calculations?
Surround the formula with the =ROUND() function. If the formula is currently A2/3, then use this formula to round to the nearest whole number:
The ",0" in the formula says that you want to round to have no digits to the right of the decimal place. If you wanted to have 2 decimal places, then use
To round to the nearest thousand, indicate a negative number as the 2nd argument for the round function
=ROUND(A2/3,-3) will round to the nearest 1000.
How can I get rid of broken links after receiving an e-mailed workbook?
There are many causes of broken links. A great place to start to look is in the Insert - Names - Define dialog box. Many named ranges may be pointing to the linked workbook. Simply delete these unused names. Another place that links may hide is in the Data - Consolidate dialog box. If you use Data Consolidate, you may find old entries hanging out in this dialog box may be the source of the unwanted link.
Microsoft offers a utility to delete the broken links, but the best utility we have found is the FindLink add-in from Hans Herber, the German version of MrExcel. If you read German, check out this page, http://www.excel-cd.de/forum/archiv/24to28/t24746.htm, where you can download http://www.herber.de/andere/findlink.zip.
We have three computers in our office. During the course of the day, each computer will have an occassion to open a particular workbook on the network. Frequently, we find that after someone has accessed the workbook, the formatting becomes lost. When the next person opens the workbook, the numbers do not fit in the columns, everything shows as ######, etc. What could be causing this?
And for this one, I will turn to the readers of MrExcel. Any ideas? If you have experienced similar problems and can suggest a solution, drop an e-mail to challenge@MrExcel.com. The best answers will be rewarded with one of the highly collectible MrExcel.com Salt Lake City 2002 Winter Olympic Calendars.
Thanks to the board of the Akron IMA chapter for inviting me to speak at their professional development evening. It was great to have a fantastic turnout and large audience for the presentation!
By Bill Jelen on 06-Dec-2001