ShareThis
|
TECHNOLOGY Q&A
Bugged by Excel's calculation errors
By J. Carlton Collins, CPA
March 2014

Q: I found an anomaly with a rather simple Excel computation; specifically, Excel calculates 111,111,111 times 111,111,111 to equal 12,345,678,987,654,300, which is incorrect (the correct answer should end in “21,” not “00”). If Excel’s arithmetic fails, then what else is false, and why does this happen?

(Author’s note: You must increase the column width size to view the full number mentioned by the reader; otherwise Excel displays the number in exponential format (1.23457E+16), which does not allow you to see the calculation problem. If Excel continues to display the number in exponential format after you increase the column width, then right-click on the cell, select Format Cells, and in the pop-up box, select Number under the Number tab.)

A: You don’t need arithmetic to expose this problem; you can’t even type 12345678987654321 into Excel. When you try, Excel displays 12345678987654300. This is not an anomaly. Excel is designed to handle only 15 total digits in a given number (including digits after the decimal place when applicable). Therefore, it is impossible to accurately type any number into Excel with more than 15 digits (ending in numbers other than zero). If you do, Excel rounds the digits after the 15th place down to zero.

This problem stems from the fact that computers store numbers as binary numbers and display them as numeric numbers. The inherent back-and-forth conversion causes problems with certain numbers. Excel follows the industry standard IEEE 754 protocol for storing and calculating floating-point numbers in computers, a standard that was officially adopted in 1985 and was updated in 2008. Support for numbers with more than 15 digits requires more computer storage and greater processing resources. Accordingly, the number of digits supported in Excel is limited to 15, in line with the industry standard in pursuit of optimum programming and processing efficiency. (Note: To ensure your understanding of this problem, it is not correct to say that Excel can’t handle numbers greater than 15 digits. Rather, we should say that Excel does not handle these larger numbers accurately, as the extra digits in excess of 15 are rounded down to zero.)

While this limitation may plague astronomers and geneticists who deal with such astronomical figures, CPAs are rarely affected by this limitation (except on rare occasions, such as when CPAs are dealing with hyperinflated foreign currencies). Still, CPAs should be aware that Excel is vulnerable to producing such errors. For example:

- In Excel 2007, multiplying 77.1 times 850 yielded 100,000 instead of the accurate answer 65,535. As explained by Microsoft at tinyurl.com/3q3rf2n, Excel calculated the result correctly (e.g., if you charted the erroneous 100,000 number, it displayed correctly on the chart as 65,535), but the result displayed on the worksheet incorrectly due to an improper conversion of the binary representation of that number into its numerical string. This particular bug has since been corrected.

To illustrate the problem further, consider that the formula 1/3 calculates to 0.3333333~ (with an infinite number of repeating 3’s as decimal places). Because Excel can hold only 15 decimal places accurately, saving and retrieving this number results in a value that is very near to, but not exactly, 0.3333333~. Similarly, certain odd numbers create repeating binary decimals, and when those repeating digits are cut off after 15 places, the binary number does not convert back accurately to the intended numeric value. As an example, in all editions of Excel, the formula 22.26 − 21.29 should yield 0.97, but instead yields 0.970000000000002. Try it, and remember to increase your column width and decimal places so you can see the calculation problem.

Such errors are typically considered insignificant or immaterial because they rarely manifest into meaningful calculation errors; nonetheless, here are two measures you can take to eliminate potential floating decimal point errors:

1. The ROUND function. Use Excel’s ROUND function to round your calculated values to the desired decimal place, thereby eliminating any possibility of 15th-digit anomalies. For example, the formula =ROUND(-21.29 + 22.26,2) accurately yields 0.97.
2. Precision. You can turn on Excel’s Precision as Displayed option to force all formulas to truncate and round calculated values based on the visible digits. To turn this option on in Excel 2013, 2010, and 2007, select File (or the Office Orb), Options (or Excel Options), Advanced, and in the When calculating this workbook section, check the Set precision as displayed box, and then click OK. In Excel 2003, 2002, and 2000, from the Tools menu, select Options, and on the Calculation tab, under Workbook options, check the Precision as displayed box, and then click OK.

TECHNOLOGY Q&A
A better letter
By J. Carlton Collins, CPA
March 2014

Q: Can you point me toward a good website that provides default business letters and contract wording so I don’t have to prepare all of my business letters and contracts from scratch?

A: Instead of referring you to a website, I will instead direct you to Word 2013’s templates, which provide thousands of standard letters and contracts. To access these templates, from Word’s File tab, select New, and click the Letters category. Use the Category listing in the right pane or the Search box near the upper-left corner to refine your search, and then click the template to download from Microsoft’s template repository, a sampling of which is pictured below.

TECHNOLOGY Q&A
By J. Carlton Collins
March 2014

Q: Our training room’s projector died, and we are in the market for a new one. Can you give us some advice as to what we should look for in a replacement projector?

A: The good news is that today’s projectors are better than ever—and far less expensive. There are many facets to consider, but only a handful are most critical. Below is my checklist for evaluating and selecting a new projection system.

1. Lumens. The most important consideration is the lumens—the brighter the better. While 2,000 lumens will get the job done in darker rooms, having 3,000 lumens usually allows you to leave the lights on and still see the screen just fine.
2. Widescreen. Since today’s laptops and monitors have migrated to widescreen displays, your projector should support widescreen as well. (Of course, if you do purchase a widescreen projector, you may also need to upgrade to a widescreen projection screen.)
3. Input ports. Make sure the new projector supports your computer’s output signal, be it VGA, HDMI, USB, DVI, S-video, component video, etc. (I find that VGA is adequate for computer displays, but HDMI is needed to support high-definition DVD or television signals.)
4. Wireless (optional). You should consider purchasing a wireless projection system so Bluetooth- or TCP/IP-enabled laptops, tablets, and smartphones can easily connect to the projector.
5. Other. Most systems that meet the above four criteria will likely provide adequate speakers, resolution, contrast ratio, ceiling mount, rear display, and keystone correction options. So these factors are typically less important to scrutinize in detail, but you should still check to make sure they are included.
6. Projector screen. The size and quality of the projection screen are very important. In my opinion, the screen must be at least 8 feet wide, or wider (even if the ceiling height is only 8 feet and the screen can’t be fully deployed vertically). The back of the screen should be solid black to prevent light from bleeding through, as this allows more light to bounce back toward the audience. The screen’s surface should be textured (not smooth) so light is also reflected sideways (in addition to straight back) to allow viewers seated near the edges of the room to see more clearly. As a comparison price, I found this 10-foot-wide Elite screen, pictured below, available (on Amazon.com) for \$249.

7. Use Windows low resolution. If you intend to project computer applications (i.e., not just PowerPoint slide shows), you should set your computer screen’s resolution to the lowest possible setting (not the highest resolution, which most people tend to use). This setting will display fonts and images larger so they are easier for the audience to see and read. To change your screen’s resolution, right-click on the Windows Desktop and select Screen resolution, then select the lowest resolution supported by both computer and projector that will display your intended content properly, as pictured below.

8. Economy mode. Ironically, many people purchase a more powerful 3,000-lumen projector and then set the bulb to 1,500 lumens (“economy” mode) to save the projector’s bulb life. This approach is akin to purchasing a 12-cylinder Jaguar and using only six spark plugs to save gasoline. I recommend that if you plan to run your projector in “economy mode,” save some money upfront by simply buying a cheaper projector that supports fewer lumens.
9. Price. As a comparison price, in November 2013, I paid \$649.99 (on Amazon.com) for a 3,000-lumen Epson EX7220 Wireless WXGA 3LCD Projector for use in delivering CPE courses.

TECHNOLOGY Q&A
Anonymous recipient
By J. Carlton Collins, CPA
March 2014

Q: Is it possible to receive email anonymously? I don’t intend to do anything illegal, but occasionally I’d like to use an anonymous email address to sign up for reading websites or for temporary communication purposes.

A: A website called Mailinator (mailinator.com) lets you create any email address and check it anonymously. For example, I sent an email to sillygoat@mailinator.com and then visited mailinator.com, entered this new email address, and instantly read the message.

Be aware that the sender is not anonymous and the email is not private. In fact, all emails received via Mailinator can be read by anyone since no password is required. (Of course, I will mention that unless encrypted, all of your previous and current emails are vulnerable to being read by anyone willing to capture and record your emails using a local-area network (LAN) sniffer device or program, both of which are widely available.) You cannot send email via Mailinator (although you can forward an email via Mailinator), and all email gets erased from the Mailinator server after a few hours.

TECHNOLOGY Q&A
By J. Carlton Collins, CPA
March 2014

Q: I recently produced several professional videos promoting our company’s services, and I’ve uploaded them on our webpage. Unfortunately, our website is hosted by one of those inexpensive web-hosting companies with a bare minimum of performance and internet bandwidth; therefore, our new videos take forever to load, and once they start playing, they stream inconsistently, pausing every six seconds or so to buffer the video stream. I am disappointed with these results, and I assume that I need to upgrade to a better web-hosting service. Can you recommend a good web-hosting service that supports faster video playback?

2. On the YouTube webpage, click the Upload button, click the large arrow icon labeled Select files to upload, and then browse to and select the video you want to upload. The upload process will start automatically.

3. As your video is uploading, a Basic info page is displayed, allowing you to add or edit the video’s Title, Description, Tags, Privacy settings, Category, and default Thumbnail image. Clicking the Advanced settings button reveals additional options for setting the Video location and Recording date, and also for controlling Comments, Ratings, License, Syndication, Captions, Embedding, Subscriber notifications, Age restrictions, 3D effects, and Video statistics settings.

4. Once the video has been uploaded, click the video (in either YouTube’s Dashboard or Video Manager view) to play it on YouTube.

5. With the video displayed on the YouTube screen, click Share, Embed to display its embed code, select the embed code, right-click atop the embed code, and then select Copy (as pictured below).

6. Using your web editing tool (I use Microsoft Expression Web, which can be downloaded free at tinyurl.com/dxr6u7g), open your webpage and position the cursor where you want the video to play, and then click Code to display the webpage’s HTML code view (as shown below). Then paste the embed code (being careful not to reposition your cursor), and save your webpage.

Thereafter, your video will appear to play seamlessly from your webpage with good performance (subject to the viewer’s internet speed), while the video is actually hosted by and playing from the YouTube website. You can view an example embedded video (as described above) at tinyurl.com/l2lfuet. (This webpage is hosted by GoDaddy.com on my asaresearch.com website, which costs me about \$39 a year.)