We've heard your feedback and are continuing to build a better Slickdeals. Click here to check out the updated site.


Excel is doing something strange Q....
I needed to retrofit a plot to plot every x points  there are about 3000 points distributed evenly over 60 cycles (seconds, here), and I wanted to modify an existing worksheet so that I could change a value in a single cell and alter the number of points plotted.The x axis values start at 0 and increment every 0.02 (1/50s) up to 59.98. The formula in each cell (A column) is A8 =A7+0.02, A9=A8+0.02, etc. So, only multiples of 0.02 should exist in this column. I replaced the y data with a column that does a logical test of the value in the x column to see if it's an even multiple of a cell. For example, =IF(A7/D2INT(A7/D2)=1,D7,"") Really it's evaluating if there's a difference between straight division and its integer representation (rounded down). For multiples we'll get, e.g. 15/5int(15/5)=33=0; for nonmultiples, we'll get, e.g. 18/5int(18/5)=3.63=0.6, so the next step is to nest that formula in an if statement. The issue is that when I extend this down the column, it doesn't work. After some digging, I find that excel is drifting in its x values. By the time the formula gets to 5, it's not 5 but instead 4.99999999999998. WTF is going on here? 

The only thing i can offer is that instead of your normal formula in column A (ex: A8 =A7+0.02) you can just round it to make sure your problem never arises A8 = ROUND(A7+0.02,2) **edit** after some googling, i am going to guess this may be why you are experiencing your drifting x values: Excel, like nearly every other computer program, uses the IEEE Standard for Double Precision Floating Point numbers. This standard is described in detail, at the bit level, in a later section of this article. We can generalize it, though, to describe how Excel stores fractional numbers. Just as computers store integers as binary numbers, they store fractional numbers as binary fractions. Computers store an integer (whole number) value as (x*1 + x*2 + x*4 + x*8 + x*16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit of off, x=0. In this notation, any integer can be stored exactly. For example, the number 13 is stored in binary as 1101 which indicates, reading from left to right, 1*8 + 1*4 + 0*2 + 1*1 = 13. Fractional numbers are stored in a similar manner. In the binary system, fractional numbers are stored as the sum of a series of fractions: (x*1/2 + x*1/4 + x*1/8 + x*1/16 etc) where x is the state of the bit. If the bit is on, x=1. If the bit of off, x=0. Unlike integers, however, not every fractional value can be stored exactly accurately. For example, it is impossible to store the number 1/10 = 0.1 in binary form. A close approximation is (0*1/2 + 0*1/4 + 0*1/8 + 1*1/16 + 1*1/32 etc). Computers carry this operation to the equivalent of 15 decimal places. Even with this accuracy, many numbers are represented as an approximation of their "true" or "analytic" value. For example, it is impossible to accurately describe the number 1/10 in 8byte (or any length) binary notation. Floating point numbers can come extremely close to representing that number, but there will always be some very small error. Last edited by Jcaelum; 10082012 at 11:18 AM.. 





I went in and maxxed out the decimals, which is where I found that what it displayed as 5 wasn't
tried rounding, same result! Last edited by Dr. J; 10082012 at 01:08 PM.. Reason: Automerged Doublepost 


That's weird ... i attached a screen cap of what i did. You can see that my 5.00 is truly 5.000000000000000 if i use the ROUND formula. If i didnt, i would get what you got (4.9999999999) 


Thread Tools  Search this Thread 