Recently in Excel Category

Weird Excel Calculation Bug

| | Comments (0)

Excel has a "bug" where calculations whose results are near 2^16 (65,536) may not be correctly displayed.

* Microsoft - http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx
* Chance News 30 - http://chance.dartmouth.edu/chancewiki/index.php/Chance_News_30

What's interesting to me is not the bug, per se, but the fact that calculations on a computer are inherently different from those on a chalkboard. Chance News and the Wolfram Blog both point out that computer calculations are all binary (base 2) at some point, while blackboard calculations are usually base 10. There's a conversion operation somewhere behind the scenes and once you get into decimals and the potential for repeating numbers, it can get tricky.

Another factor is that apparently multiplication is really done via addition of logarithms and lookup tables in order to save processing cycles. In other words, more conversions. This is not how I was taught multiplication in 5th grade.

The bug is pretty rare, so I will still be using Excel. But it's at times like these I do understand "old-school" math teachers wanting to make sure students still know how to do long division by hand. After all, these errors were probably caught by people who realized that the answers from Excel "didn't look right."

Excel 2007 New Conditional Formatting Tricks

| | Comments (0)

If you have Excel 2007, you can add value bars or color coding to your cells depending on their value. It could be handy for some of those reports we write.

http://www.databasejournal.com/features/db2/article.php/3690046

Manipulating Names in Excel

| | Comments (0)

Some days, I spend a surprising amount of time massaging data in Excel in order to move them from database to another.

On this round, I was working with names (sometimes all capitals, sometimes not), plus the original source had the name in one field while my new database had them split between two fields. These little tips helped me clean up my data:

I. Splitting Names into Two Columns
Originally from Laura J's Blog

1. Add two blank columns AFTER the full name. Add three columns if middle names/initials are included.
2. Go to the Data » Text to Columns
3.Check the Delimited Option, then check your name seperator


a. Check the space option if the order is "FirstName LastName"
b. Check the comma option if the order is "LastName, First Name". If you still have middle names in the second column, you might have to do another split based on space.


II. Changing Cases
These three formulas will let you fix inconsistent capitalization.

·=Proper(A1) - Capitalizes just the first letter of whatever is in Cell A1
·=Upper (A1) - Capitalizes all letters in whatever is in Cell A1
·=Lower(A1) - Changes all letters to lower case (this is great for cleaning up e-mail addresses)

To use the formulas
1. Insert blank columns and use appropriate formulas to convert the names
2. After you finish your conversions, you have to convert the formulas to values if you want to delete the original messy columns. To do that:
a.Copy contents in new column (the formulas)
b. Go to Edit » Paste Special. Select the Values option. This converts formulas to their values.

Now you can delete original messy columns and keep pristine newly formatted columns. This was definitely faster and less error-prone than retyping names from scratch. Thanks Laura J and Microsoft.