Problem charting with dynamic ranges

I'm having a problem with plotting charts with dynamic ranges that is driving me up the wall at the moment.

The attached spreadsheet has six columns of data (Avals to Fvals) and cells H2 and I2 to determine the start of the series plotted, and the number of points to plot. The idea is eventually to attach these to scrollbars to allow interactive zooming and panning of the data on the chart.

I have defined six named dynamic ranges Arange to Frange using the OFFSET command - as far as I can see identically with the exception of the start column in each range.

The chart plots the data from Arange. Everything works correctly and if I adjust the values in H2 and I2 it selects a different part of the data correctly.

To change which range is plotted, if I click on the data series in the chart I can edit the SERIES function that appears in the formula bar to change it to Brange, and this works correctly. It also works correctly if I change it to Drange, Erange or Frange.

So the formula looks like

=SERIES(,,dynamicChart.xlsx!Frange,1)

However, if I change it to Crange instead, I am unable to update the formula. It reports no error, but the series doesn't update - in fact I can't do anything at all - if I hit Enter or click on the tick, nothing happens, and moreover I can't click outside the formula bar, for example to a cell; the cursor remains inside the formula bar. It will only allow me out if I change it to something different.

I cannot think of any logical reason for this behaviour - what is more, there is nothing wrong that I can see with the formula defining the named range

=OFFSET(Sheet1!$C$2,Sheet1!$H$2,0,Sheet1!$I$2,1)

and the others are all identical with, eg $A$2 instead of $C$2

If I do any other arithmetic on the named Crange (e.g. set a cell to =SUM(Crange) it produces the correct answer.

If I hit F5 and goto Crange, the correct range of cells is highlighted.

Only it obstinately won't plot in a chart.

I have found only one way round this problem, which is to define the range concerned as something different (e.g. the same as Arange). Then plot a chart with Crange selected, edit the series statement to have Crange in it, and then edit the definition of Crange in the Name Manager.

I first encountered this problem on Excel 2010, but the anomalous behaviour is exactly the same on Excel 2013.


This doesn't make any sense at all, can anyone help?

I have attached the file dynamicChart.xlsx, which exhibits the behaviour.

Regards,
Iain Strachan

Hello Iain
Perhaps a better way might be to have one formula for your chart data and a Data Validation drop down to select the chart values to display. See attached reply.

Hope this helps.
DBY

Thanks for the ingenious solution, which I may well implement. But are you able to explain why mind didn't work? Is this a bug, do you think? My range formulae are all the same, and work correctly in making the selection - it just won't plot for that one label.

HI, again DBY - I find I can replicate the exact same problem with the spreadsheet you sent. (dynamicChart_Reply.xlsx)

To replicate the problem - click on the time series in the graph, so the following formula is displayed in the formula bar:

=SERIES(Sheet1!$M$2,,dynamicChart_Reply.xlsx!ChartData,1)

Now click the mouse anywhere in the word "ChartData".

Now hit return. Nothing happens. Hit the tick button. Nothing happens. Try to click anywhere else on the spreadsheet (say on a cell). Nothing happens.
The only way to get out of it is to delete the formula entirely.

Thank God, at least I'm not going mad!!

I simplified it by getting rid of the offset and just having fixed ranges, and then same problem occurred - names av and bv were fine, but cv showed the bug. change the name cv to xv in name manager and it works correctly.

However, it's not just beginning with c - the original label that showed the problem was "rrvals".

What a ridiculous and irrational type of bug! I guess if this was reported to Microsoft in 2007 and here we are with it still there in Excel 2013, then there isn't going to be a bug fix any time soon!

I think I have found a partial explanation of the bug - due to this instruction at MSDN

NOTE You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.

This was discussed (in the context of VBA) here: http://stackoverflow.com/questions/1...t-begin-with-c

Now of course you can use labels BEGINNING with an R or a C and they behave in general just like any other - until you get to use the label in a SERIES formula, when it fails. Evidently the programmers are unable to distinguish between a string of length 1 and of length greater than 1 in the formula editor.

Thanks for the update. Now at last we know there is some logic, as of course there must be with any computer.

Originally Posted by DBY

Thanks for the update. Now at last we know there is some logic, as of course there must be with any computer.

Well, of course it could be deliberate irrationality on the part of the programmer! Do you know about the infamous 0.64 bug that was present in Excel 5.

You got it by typing in as a formula: =2^47/10^15

If you do this you get an exact 15 digit number

However, if you typed this number in explicitly, or alternatively if you did copy/paste special/values or had the exact value in a csv file which you read in, it would display the value 0.64

There were about 8 other instances where a power of 2 divided by a power of 10 would give the wrong power of 2 divided by the wrong power of 10, but only if the number was present as data, not a formula.

A rumour went around that it was a deliberate artefact introduced by MS to check if someone had "reverse engineered" their Excel engine - just by typing in the 15 digit number and getting 0.64. I find this hard to believe, because surely examination of the binary code would reveal that someone had pinched the engine.

But it was a truly weird bug!

I don't really understand the coding under the hood so I guess I'll have to trust them all. Next you'll be telling me they never landed on the Moon!