Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    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 y-Values.
    I have attached an example file.
    Did I find an Excel-bug? I am sure, that this would work in Excel XP (though I did not test it).

    Any comments would be helpful.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Plutonium Lounger
    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)
    Attached Images Attached Images
    • File Type: png x.png (8.4 KB, 0 views)

  4. #4
    2 Star Lounger
    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 Excel-programming 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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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 semi-colons)

    Here is a copy

    Steve
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    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.

  7. #7
    2 Star Lounger
    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 semi-colon. It seems, that I forgot to "translate" this as well...

  8. #8
    2 Star Lounger
    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...

  9. #9
    Plutonium Lounger
    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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    2 Star Lounger
    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 Excel-bug...
    Thanks for your help!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

  13. #13
    2 Star Lounger
    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 y-values 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...

  14. #14
    Plutonium Lounger
    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.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •