Results 1 to 15 of 21

20080804, 02:46 #1
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel 2007 SP1 (Using Names in Chart Series)
Hello everybody,
I would like to use names in chart series formulas instead of sheet ranges to draw a circle. Behind the names are formulas as follows:
<pre>phi=(ROW(OFFSET($A$1,0,0,21,1))1)/10*Pi()</pre>
This delivers an array with 21 elements valued from 0*Pi() to 2*Pi() in steps of 0.1*Pi()
The center point is defined by two names:
<pre>x_c=Tabelle1!$B$5
y_c=Tabelle1!$C$5</pre>
This is the radius of the circle:
<pre>r_c=Tabelle1!$B$9</pre>
This calculates the x values of the circle:
<pre>x_circle=x_c+COS(phi)*r_c</pre>
An this delivers the y values:
<pre>y_circle=y_c+SIN(phi)*r_c</pre>
The Series formula for the xy chart should look like this:
<pre>=SERIES("My Circle";ChartTest.xlsx!x_circle;ChartTest.xlsx!y_c ircle;1)</pre>
Unfortunately, I cannot make it work. The formula won't accept the part for the yValues.
I have attached an example file.
Did I find an Excelbug? I am sure, that this would work in Excel XP (though I did not test it).
Any comments would be helpful.

20080804, 02:54 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Could you attach a version saved to a older version of XL for those of us who can not open XL2007 files, or is this problem only with XL2007?
Steve

20080804, 02:59 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
It works for me in Excel 2002  see the screenshot below (REEKS is the Dutch name of the SERIES function)

20080804, 03:05 #4
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Thanks to both of you for taking care of this. It seems that it is a problem with XL2007 only. The idea for this is from the book "Professional Excel Development" (Addison Wesley, the best book about Excelprogramming I know, thanks to the authors!!).
It seems, that in 2007 I can only use the name for the x values, y won't work for me.

20080804, 03:06 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
I have created what you describe in XL2002 and it works for me as well. (In US version the SERIES uses commas, not semicolons)
Here is a copy
Steve

20080804, 03:08 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
I'm afraid I don't have Excel 2007, so I can't test there. I can't find anything about this problem on Google or the newsgroups.

20080804, 03:16 #7
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Hello Steve,
sorry, I am working with a german version of Excel, which uses a semicolon. It seems, that I forgot to "translate" this as well...

20080804, 03:18 #8
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Hello Hans,
thanks for googling about this. I also tried to find out something about this  without any success. This problem drives me insane, since I try to find a reason for this for a couple of days now.
If I find something on this one, I will post it here, but hope is shrinking...

20080804, 03:21 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Some Loungers do have Excel 2007, hopefully one of them will test it there.

20080804, 03:47 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
I can not test in XL2007, If you open my XL2002 version in XL2007 does it work or not?
Steve

20080804, 05:02 #11
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Hello Steve,
sorry, I had to attend a meeting, this is why I could not test immediately. It is amazing, but your worksheet works as expected. But if you change the reference within the formula to a regular range and try to reproduce this, you won't be successful.
It seems this is an Excelbug...
Thanks for your help!

20080804, 06:04 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
I don't know what you meant by this: if you change the reference within the formula to a regular range and try to reproduce this, you won't be successful."
What exactly did you do to make it "fail"? it may not be a bug so much, but doing something "incorrectly"
Steve

20080804, 06:56 #13
 Join Date
 Mar 2002
 Location
 Germany, Germany
 Posts
 169
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
Well, I did nt do something incorrectly, I am sure. What I meant is the following:
I changed the Series formula by hand, typing in a cell reference for the yvalues directly into the formula. That worked (as expected). Result was a formula you get, when you select a data area and create a chart.
Then I typed in the named references back into the formula. In fact I copied your formula into the clipboard and pasted it back into the "Bearbeitungsleiste" (what is the english word for that? It's the line directly under the ribbons). The formula was exactly the same again, but I could not confirm it by pressing Enter. I experienced the same problem initially mentioned in my post.
By the way, with Excel XP I could do is intended. Opening the file in 2007 worked also as expected (like your file).
I am desperate...

20080804, 07:30 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
The "Bearbeitungsliste" is the formula bar in English, I think.

20080804, 07:49 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel 2007 SP1 (Using Names in Chart Series)
I either do not completely understand what you did or it does not happen in XL2002. Unfortunately I can not test in XL2007...
What happens if, instead of the "Bearbeitungsleiste" you edit it in the "Source data" by right clicking on the chart?
Steve