What obvious thing am I missing?! (assigning correct axes and labels for a scatter plot in Excel)

[Vorax]

Hi!

I can't figure out how do something that seems conceptually quite straight forward and I'm hoping someone can show me something obvious that I'm missing!

• I have a list of Factors – each has been scored on a nine-point scale on a second list of variables
• I have chosen two of the variables (X and Y) and calculated % shared variance scores between each of those variables and all of the other variables (A, B, C, D etc).
• I therefore have a table with a row each for X and Y, and columns for A, B, C, D, E, etc. In each cell I have a score for the % shared variance that the Factor in the column heading shares with the X (for the X row) and Y (for the Y row).

I now want to plot A, B, C, D, E etc on a scatter plot, where the X axis is the % shared variance with X and the X axis is the % shared variance with Y. I want each plot to be labelled with the factor name from the column heading (“A”, “B”, “C” etc…)

For example. If the variable A has a 60% shared variance score with X and a 5% shared variance score with Y, the position of A on the chart would be plotted at 5% along the Y axis and 60% along the X axis.

I've attached some example data and a mockup of the type of chart I'm trying to create...

It would be great if someone could tell me how to do this in Excel.

Ideally I would like to add a third variable Z to form an X-axis and make a 3D scatter plot!

Thanks!

 

[vletm]

Vorax
Something like this (2D)?

 

[p45cal]

It would save us time if you attached a workbook too. See attached. The labels are directly above each point.

 

[vletm]

Vorax
For Your Ideally I would like to add a third variable Z to form an X-axis and make a 3D scatter plot!
It needs more details - what kind of clear image do You have about that?

 

[Vorax]

Thanks both, that's exactly what I'm looking for :D

After examining what you've done in the the 'edit series' window in your files I can now see how to get the data points to plot correctly - however, I can't work out how set the data labels to A, B, C etc - my labels are all values from specific cells in one of the two axis ranges.

RE 3D scatter plot - I wanted something very similar this2D version, but with an additional Z axis - something like the attached, but with standard scatter plot dots and the A, B, C data labels.

Thanks!

 

[p45cal]

my labels are all values from specific cells in one of the two axis ranges.

RE 3D scatter plot - I wanted something very similar this2D version, but with an additional Z axis - something like the attached, but with standard scatter plot dots and the A, B, C data labels.

Where are the 'specific cells'?
Where are the Z values?
This is why attaching a workbook would be useful. Save us time, make it easy for us, and stop us guessing wrongly, get an answer that works for you. Mock up a workbook like we each have; help us to help you…

 

[Vorax]

Apologies, file attached, with where I've got up to with the chart.

If it helps to know, the variables in this worksheet are a persons psychological constructs that they use to make sense of their social world, "Has direction", "clarity of Thought", "Tactful" etc. This person has rated a list of people they know on each of these constructs, which has allowed the creation of an matrix to show how much each of their contructs is correlated with one another (and therefore shares a similar meaning for this person - e.g. "focused on others" and "puts others needs first"). Because correlation coefficients are non linear, to enable useful visual representation these are then squared and transformed into percentages to create shared variance scores, as presented in the attached file.

So I want the X axis to be the % shared variance that each other variable shares with the row 2 variable "Happy", the Y axis to be the % shared variance that each other variable shares with the row 3 variable "has direction" and the Z axis to be the % shared variance that each other variable shares with the row 4 variable "Assertive". [Non essential info: The X axis is the variable the accounts for the most shared variance (in this case 'Happy', the Y axis is the variable that has the next highest shared variance that is NOT significantly correlated with the X axis ('Has direction'), similarly for the Z axis ("Assertive")]

The label for each plot point should be the names of the variables.

FYI - I will ultimately reduce the number of variables in the plot - before i do so I need to see how many will fit on with data labels before it becomes too crowded.

Hope that makes sense!

Thanks!

 

[vletm]

Vorax
About Your however, I can't work out how set the data labels to A, B, C etc - my labels are all values from specific cells in one of the two axis ranges.
Both of those sample solutions has used same - how to set the data labels ...

Did You notice above?

Your 3D:
I quickly tested to do one sample.
I added my sample output with red to Your chart.
Something like that?

 

[p45cal]

A guess; like this:?



See attached. Select the chart and drag the grab-handle to adjust what's plotted. I've only plotted a part of your data.

 

[Vorax]

Thanks both - that's great I now know how to do what I need to do in 2D.

I think vletm's approach to adding the Z-axis is what I'm after, although I'd need to see the chart displaying a clearer Z axis to be sure.

p45cal - your chart has the 3d look and feel I'm after in terms of a grid in 3d space - and would be ideal with the following changes: the scale for each axis should be %shared variance, and the data label for each plot point would be the name of a variable - for example we might have a plot point labelled "kind" that is plotted at 70% X, 30% Y and 40% Z.

Thanks!

 

[vletm]

Vorax
Your I'd need to see the chart displaying a clearer Z axis to be sure.
You could test to change Your original Z-axis values eg manually.
> Eg from 5% to 100% with 5% steps - then You could see how that value changes red spot position based 2D-position.
> Each movement of Z-value is half of X-& Y-values movements.
After that - You could hide Your original 2D-spots and it will show something like Your expected sample of chart-picture.
Or
How would You be more sure?

 

[Vorax]

Vorax
Your I'd need to see the chart displaying a clearer Z axis to be sure.
You could test to change Your original Z-axis values eg manually.
> Eg from 5% to 100% with 5% steps - then You could see how that value changes red spot position based 2D-position.
> Each movement of Z-value is half of X-& Y-values movements.
After that - You could hide Your original 2D-spots and it will show something like Your expected sample of chart-picture.
Or
How would You be more sure?

After re-examining your file I actually don't think that's what I'm looking for - it still looks like a representation of "D space using just an X and Y axis - I need something that clearly looks like a 3D space so that it's easy to visually see the relative position of each point in three dimensions - I'm not sue if that is possible to do in Excel?

Thanks!

 

[vletm]

Vorax

Isn't above - what are You looking for?
and
Below shows based You data something with 3D?

Below based Your 3D scatter plot - same with normal X & Y-axes with Z-axe.
Difference is ... below charts X- & Y-axes are in different angle than ... what are You looking for?
It is possible to do something more like Your what are You looking for?
but it means to do it without basic Excel-charts.
OR
It's possible to do with 3D Column -chart.

You missed data labels can show there.

There could hide some rows of data.
Many things are possible to do with Excel too.

 

[vletm]

Vorax

 

[Vorax]

Thanks.

In order to properly conceptualise and make sense of my specific type of data really need to be able to see each axis with its scale in a clear 3D space, and with plot points corresponding to each variable - bars dont make sense in the this case.

Here is an another example of how i need it visualised (although this example would need variable names adding to each plot point):

3D scatterplot — Matplotlib 3.8.4 documentation

matplotlib.org

I'm now thinking that this type of chart is not possible with Excel?

Thanks!

 

[vletm]

Vorax
In order to properly conceptualise and make sense of my specific type of data really need to be able to see each axis with its scale in a clear 3D space, and with plot points corresponding to each variable - bars dont make sense in the this case.
If that data is something same as You've given
It would look something same as my the 1st sample.
.... There could see two axes smoothy and z-axes - could be need more?
Q: How could You able to see - what are position of plots in those axis ... if more 3D-looking?
A clear 3D space ... would need a huge monitor to see - something like A0-size printout.

Here is an another example of how i need it visualised (although this example would need variable names adding to each plot point)
Isn't it basic same example as You've already shown?
... as well as without those labels.
Would it need to rotate around with each axes too?

I'm now thinking that this type of chart is not possible with Excel?
It's possible to make,
but could someone who'll see it - could read it as You've written?

Something like #14 reply's sample -- it would be more possible.

 

[Vorax]

Vorax
In order to properly conceptualise and make sense of my specific type of data really need to be able to see each axis with its scale in a clear 3D space, and with plot points corresponding to each variable - bars dont make sense in the this case.
If that data is something same as You've given
It would look something same as my the 1st sample.
.... There could see two axes smoothy and z-axes - could be need more?
Q: How could You able to see - what are position of plots in those axis ... if more 3D-looking?
A clear 3D space ... would need a huge monitor to see - something like A0-size printout.

Here is an another example of how i need it visualised (although this example would need variable names adding to each plot point)
Isn't it basic same example as You've already shown?
... as well as without those labels.
Would it need to rotate around with each axes too?

I'm now thinking that this type of chart is not possible with Excel?
It's possible to make,
but could someone who'll see it - could read it as You've written?

Something like #14 reply's sample -- it would be more possible.

RE: It would look something same as my the 1st sample.
Ok - so now I'm confused - in your first example I can only see two axes - X and Y - where is Z?

RE: but could someone who'll see it - could read it as You've written?
- The visualization is primarily for me to interpret psychological assessment results. I believe that others will read it in the same way as I do, but it will be interesting to see if I'm right!

ok, so I'm re-looking at #14 - bigger spheres are closer (and have lower scores on the Z scale) and vice versa - is that correct?

 

[vletm]

Vorax
#1 Did You skipped #11 reply?
Did You test as I wrote?
... then You could see - matter of Z-axes.

#2 Your data could have all same values.
Then there would be visible ... one spot ... with 32 labels.
Is it a challenge?
You could see based any output (even from Your sample data) - how do it looking?

#3 Yes - You could get an image based Your data.
... but as it's a sample - it could be more clear.

 

[Vorax]

Thanks.
#1 see attached for my attempts to follow your instructions from #11 - I think I've missed something?

#2 if any variables are very close on in the 3D space I'll either remove one, or concatenate the labels

#3 thanks for confirming - I will experiment more with this approach.

 

[vletm]

Vorax
#1 Yes, You've thought correct ...
You could test to change Your original Z-axis values eg manually.
Instead above - You seems to fill manually all (X, Y & Z) values.
As You could notice ... rows 36... 38 has formulas.
Think eg next three pair of plots effect with.
> Ambitious 17%, Self confidence 54%, Empathic 7%
Your original plots are blue and with Assertive red with Labels


#2 ... hmm? Manually? ... how would You compare different results?
Or
> If You have Your data in Table ... then there could use Slicers.
> What about groups? .. and show eg averages

#3 It would show those values like 3D.