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

1. ## 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. ## 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

3. ## 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?

4. ## 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

#### Thread Information

##### Users Browsing this Thread

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

#### 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