+ Reply to Thread
Results 1 to 4 of 4

Any idea how to build in Excel a correlations cylinder, or circle?

  1. #1
    Registered User
    Join Date
    06-30-2018
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    9

    Any idea how to build in Excel a correlations cylinder, or circle?

    Hi,
    Any idea how one can build in Excel chart (correlations cylinder, or circle) like in the picture?
    Untitled.jpg
    Thank you.
    Any help will be greatly appreciated.
    Galina

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,637

    Re: Any idea how to build in Excel a correlations cylinder, or circle?

    Here are points on a circle. Plot them as points and additional series as lines to connect them.

    A
    B
    C
    D
    E
    1
    Point
    Angle
    x
    y
    2
    FnPn
    0
    0.000
    1.000
    C2: =SIN(RADIANS($B2))
    3
    TnPn
    24
    0.407
    0.914
    D2: =COS(RADIANS($B2))
    4
    Fcnp
    48
    0.743
    0.669
    5
    Tcnp
    72
    0.951
    0.309
    6
    Anp
    96
    0.995
    -0.105
    7
    Acp
    120
    0.866
    -0.500
    8
    Ach
    144
    0.588
    -0.809
    9
    K8
    168
    0.208
    -0.978
    10
    F8
    192
    -0.208
    -0.978
    11
    Kuo
    216
    -0.588
    -0.809
    12
    Fuo
    240
    -0.866
    -0.500
    13
    M8
    264
    -0.995
    -0.105
    14
    X8
    288
    -0.951
    0.309
    15
    M40
    312
    -0.743
    0.669
    16
    X40
    336
    -0.407
    0.914
    17
    18
    FnPn
    0.000
    1.000
    C18:D18: {=INDEX($C$2:$D$16, MATCH(A18, $A$2:$A$16, 0), 0)}
    19
    Fcnp
    0.743
    0.669
    20
    21
    FnPn
    0.000
    1.000
    22
    F8
    -0.208
    -0.978
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,346

    Re: Any idea how to build in Excel a correlations cylinder, or circle?

    A quick internet search finds this Wikipedia page: https://en.wikipedia.org/wiki/Factor..._of_mixed_data Is this the kind of thing you are doing?

    How much of your question is the math and statistics behind the analysis, and how much is just about drawing the circle and lines?

    Drawing the circles/lines should be easy on an Excel scatter chart. Compute the x and y values for the circle and add the circle as one data series. Compute the x and y values for the nodes, and arrange them in the desired order and add as additional data series to get the lines connecting points on the circle. (See shg's post above)

    I don't know if we have anyone on this forum who will be familiar enough with factor analysis of mixed data to teach you how to do it in Excel. I looked at the Wikipedia article quickly, and could tell that there is a lot of math/stats involved. If you can help us understand what part of the computations you are having trouble with, I expect we can help you with programming those parts of the analysis.

    I note that the Wikipedia page references an R package that will allegedly do this. Would it be better to do this in R so you can take advantage of that package?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    06-30-2018
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    9

    Re: Any idea how to build in Excel a correlations cylinder, or circle?

    Folks, thank you both!
    You did serious work to answer!
    To MrShorty: Thanks God, I only need to make a picture for an article of a friend of mine. I wouldn't even dream about factor analysis of mixed data, although I feel that it must be very interesting.
    I like very much how you try to understand my question much deeper that I even thought about, thank you.
    Galina

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transferring correlations between excel sheets
    By baldmechanic in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-03-2014, 03:08 AM
  2. How to make a Clustered Cylinder Chart in Excel 2013?
    By Anodos in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-11-2014, 04:35 PM
  3. Replies: 4
    Last Post: 12-17-2012, 11:45 PM
  4. Replies: 2
    Last Post: 06-07-2012, 09:20 AM
  5. Clustered Cylinder Graph
    By steph67 in forum Excel General
    Replies: 4
    Last Post: 12-19-2010, 01:02 PM
  6. Replies: 1
    Last Post: 08-11-2006, 10:10 AM
  7. [SOLVED] How do I create a one cylinder graph?
    By Ana in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-15-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1