Browse by Tags

In the past two posts I talked about all of the improvements we have made to the function library in terms of accuracy and consistency. In this post I will describe the UI changes we have made to help users take advantage of these improvements in Excel 2010. In addition I will also talk about the backward compatibility experience for the new function library. Function Improvements UI Changes As a result of the changes we’ve made to improve the consistency of Excel’s function library, we have introduced...

Thanks to Jessica Liu for putting together the next few posts on function improvements. In Excel 2010, we made many improvements to Excel's function library. Excel 2010 will feature an accurate and consistent function library while remaining compatible with previous versions of Excel. In this first blog post, I will be giving an overview of the work we did in this area as well as talk about the function accuracy improvements. Subsequent posts will go into the details of the consistency improvements...

Today’s author is, Ron de Bruin, an Excel MVP . You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/ There are four primary week numbering systems in use worldwide. Each system has subtle differences that you should be aware of. Excel can work with any of these systems: 1)  ISO Week number: The International Organization for Standardization (ISO) ISO8601:2000 Standard. All weeks begin on a Monday. Week one starts on Monday of the first week...

I’m sure many of you have built a spreadsheet like this before: you’ve got a table of data; one of the columns in this table contains a formula which references a span of columns in the same table. The number of columns you need to reference may change over time as requirements change. How do you you build this table in such a way that the number of columns can be changed without breaking the formula that references them? Let’s walk through a somewhat contrived but simple example to demonstrate the...

Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, gives an overview of some new functions in Excel 2007 and along the way shows us how to do some simple expense tracking. Considering the current economic environment, one may desire to better keep track of personal expenses. The new functions introduced in Office Excel 2007 simplify this effort. Here is how: given a list of expenses sorted by dates and categories (food, travel, clothing, etc.), how do I find how much...

Today's author, Charlie Ellis, continues discussing the spreadsheet he built to solve Sudoku puzzles. In my previous post , I walked through a number of formulas for setting up the valid values and solution board. In this post we'll cover using iteration and other formula tricks to help solve the puzzle. Using valid values to drive solutions Looking at cells P22:R24 below, we can clearly see that the only solution here is 7: Let's reflect this in the solution board. We're going to...

We sometimes get mails from our customers claiming to have found a calculation error in Excel, when in fact the calculation isn't wrong, but the side effects of binary floating point precision make it seem that way. Today's author: Jessica Liu, a Program Manager on the Excel team, discusses the way Excel performs calculations, explains why sometimes you see answers you may not expect, and provides some tips on how to avoid rounding issues. Take a look at the following table: I want to be able to quickly identify the cases where the absolute difference is greater than or equal to 0.005. So I apply a conditional formatting rule on the absolute difference column to format values greater than or equal to 0.005 to be red. As a scan down the table, I notice that the value of 0.005 is not highlighted. I check over my conditional formatting rule and the formula I used to calculate the absolute difference (=ABS(A2-B2)), they seem to be correct. I then increase the precision of the absolute difference column in order to get more precise results. I discover my results have changed. Why does 1.320 - 1.3190 = 0.0049999999999999? Have you ever encountered a similar situation where your spreadsheet does not give you the result you were expecting for a seemingly simple calculation? You have checked over your calculations and still cannot figure out where it went wrong. Well the scenario you are facing may be due to floating point precision. Overview Excel was designed in accordance to the IEEE Standard for Binary Floating-Point Arithmetic ( IEEE 754 ). The standard defines how floating-point numbers are stored and calculated. The IEEE 754 standard is widely used because it allows-floating point numbers to be stored in a reasonable amount of space and calculations can occur relatively quickly. The advantage of floating over fixed point representation is that it can support a wider range of values. For example, a fixed-point representation that has 5 decimal digits with the decimal point positioned after the third digit can represent the numbers 123.34, 12.23, 2.45, etc… whereas floating-point representation with 5 digit precision can represent 1.2345, 12345, 0.00012345, etc… Similarly, floating-point representation also allows calculations over a wide range of magnitudes while maintaining precision. For example, Floating-point representation that has 4 digit precision: 1.1x10 -1 x 1.1x10 -1 = 1.44 x 10 -2 Fixed-point representation that has 4 digit precision with the decimal point positioned after first digit: 0.110 x 0.110 = 0.012 All numbers expressed in floating-point format are rational numbers. Irrational numbers such as π or , or non-terminating rational numbers must be approximated. The number of digits of precision also limits the accuracy of the numbers. Excel store 15 significant digits of precision. For example, the number 1234567890123456 cannot be exactly represented if 15 digits of precision are used. The IEEE 754 floating-point standard requires that numbers be stored in binary format. This means a conversion must occur before the numbers can be used in calculations. If the number can be represented exactly in floating-point format, then the conversion is exact. If not, then the conversion will result in a rounded value which will represent the original value. Numbers that appear exact in the decimal format may need to be approximated when converted to binary floating-point. For example, the fraction 1/10 can be represented in the decimal format as the rational number 0.1. However, 0.1 cannot be represented precisely in binary floating-point of finite precision. 0.1 becomes the repeating binary decimal 0.0001100110011…, where the sequence 1100 repeats infinitely. This number cannot be represented in a finite amount of space. So in Excel, it is rounded down by approximately 2.8E-17 when it is stored. Structure of a Floating Point Number A floating-point number is stored in binary in three parts within a 65-bit range: the sign, the exponent, and the mantissa. The Sign Bit The sign stores the sign of the number (positive or negative). 0 represents a positive number while 1 represents a negative number. The Exponent The exponent stores the power of 2 to which the number is raised or lowered. The exponent field needs to be able to represent both positive and negative exponents. To avoid having to store negative exponents, a bias value is added to the actual exponent. The bias for double-precision numbers is 1023. For example, a stored value of 1000 indicates an exponent of 1000 - 1023, or -23. The Mantissa The mantissa stores the actual number. It is composed of an implicit leading bit and the fraction bits. The storage size of the mantissa determines how close two adjacent floating point numbers can be. The mantissa and the exponent are stored in separate components. The precision of a number varies depending on the size of the mantissa. Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E...