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/D2-INT(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/5-int(15/5)=3-3=0; for non-multiples, we'll get, e.g. 18/5-int(18/5)=3.6-3=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?

Quote from Dr. J : 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/D2-INT(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/5-int(15/5)=3-3=0; for non-multiples, we'll get, e.g. 18/5-int(18/5)=3.6-3=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?

hmmmm interesting. i tried in on my computer (excel 2007, fresh sheet) and i notice the same thing. For me, it starts exactly at 4.34 going to 4.36. I am not sure why this is happening, must be one of Excel's many calculation quirks.

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 8-byte (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.

Quote from Dr. J : 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/D2-INT(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/5-int(15/5)=3-3=0; for non-multiples, we'll get, e.g. 18/5-int(18/5)=3.6-3=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?

I'm going to keep it simple before delving into the greater part of the spreadsheet, but have you changed for format for the cells so it reflects 2 decimal places only after the number?

Quote from menace33 : I'm going to keep it simple before delving into the greater part of the spreadsheet, but have you changed for format for the cells so it reflects 2 decimal places only after the number?

I went in and maxxed out the decimals, which is where I found that what it displayed as 5 wasn't

Quote from Jcaelum : hmmmm interesting. i tried in on my computer (excel 2007, fresh sheet) and i notice the same thing. For me, it starts exactly at 4.34 going to 4.36. I am not sure why this is happening, must be one of Excel's many calculation quirks. 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 8-byte (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.

tried rounding, same result!