Series Issue Within Charts Once Links Are Broken

[Legacy 251696]

I have posted this on the Home - Microsoft Community site but I thought I would post this other places just to see if anyone is familiar with this issue.<o></o>
<o> </o>
I have a very peculiar issue with series in charts that have been converted to values once links are broken. I cannot see a discernable pattern of numbers that cause the issue; however, it is very easy to recreate. There needs to be a workbook that contains data and a workbook that contains the chart. On the workbook that contains data, enter in 0.29 for any number of periods, let’s say a total of ten. All periods would be the same number; 0.29.<o></o>
<o> </o>
Now create a line chart with markers in a different workbook and link to that set of numbers (the 0.29 set) and save this workbook. Now there should be two workbooks and the one with the chart is linking to the one with the data. Break the links and save the file with a new name. Clicking on the chart’s series will reveal something that looks like this:<o></o>
<o> </o>
=SERIES("test",,{0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29,0.29},1)<o></o>
<o> </o>
This appears to be normal and everything is working fine. Now close the two files which will end this instance of Excel and clear all memory. Next, open the file that is not linked and once the series is selected, the error is obvious. This is what the series looks like after the instance has been restarted:<o></o>
<o> </o>
=SERIES("test",,{0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999,0.289999999999999},1)<o></o>
<o> </o>
I believe this is a floating point issue and while this is not an issue within cells, Excel can’t handle the conversion within charts.<o></o>
<o> </o>
Any ideas on a work around?<o></o>

 

[Legacy 251696]

Just a quick update on this issue. While I'm confident this is a binary issue, charts that are asked to retain certain information after links have been broken are subject to other found issues besides the one above. Two other issues I have found: 1. If the chart was linked to a range of cells and some of those cells were blank, the chart correctly displays zero; however, once the links have been broken the chart will encounter a mathematical error with the soft zero (Excel placed it there as opposed to having a zero in the cell) and therefore the chart contains a #QNAN error. 2. Similar to the first point, if the chart is not to graph zeros, then #N/A will prevent the line from being displayed; however, same issue as above and there is a -1.#QNAN error with #N/A.</SPAN>

With all that said, the only solution I can think of is to have a hidden sheet containing the values for the chart and then once the links are broken the charts will be maintained. This is not very practical because I have written thousands of lines of code across multiple modules. I am not so keen on editing all this code so I still don't have a great, or even good, solution.</SPAN>