0

I have the following data with me:

Mean - 25
-SD - 21
+SD - 29

Value to plot = 26. How can I represent this in Excel? So imagine this like 3 columns (for Mean, -SD and +SD) with Mean in the middle and -SD and +SD on either sides, and I have to create a 4th column to represent 26 in a different color.

Here's a representation -

enter image description here

1
  • I have updated the post – CuriousDev Aug 6 '16 at 18:27
1

To show one way without changing the values itself:

Make A1:E1 to y,-SD,Mean,+SD,Value. A2:E2 would be 0,21,25,29,26. A3 is 1 and B3:E3 will be the same as B2:E2.

The sheet should look like this:
enter image description here

Now select the whole range (A1:E3) and hit scatter chart in the insert tab.
enter image description here

Do not get confused, you will get a chart which looks like this:
enter image description here

Now we right click the chart and hit "Select Data..."
enter image description here

In the new window we hit the middle button "switch row/column" so the chart looks like this:
enter image description here

This is already pretty close to what we want. To transpose it (so it looks like your picture) we need to switch all x and y values (which is a bit more of work). We again go to the "Select Data..." window. There simply select the first series (should be -SD) and hit edit to get another window holding the data:
enter image description here

We now need to set the "Series X Values:" to the range which is in "Series Y Values:" and vice versa. The series name should not be changed (it is easier to know which "line" is which).
enter image description here

We need to to do this "switch" for each series. so in our example the y values always should be =Sheet1!$A$2:$A$3. After all of that is done, the chart should look like this:
enter image description here

All that is left is formatting. Hide gridlines/legend/scales, change colors for the series all that stuff. I also suggest switching the the "values series" to first position in the "Select Data..."- window (so if the value is the same to +/-SD or Mean it doesn't "vanish" behind it). If everything is done, then it can look like this (just format it to fit your needs):
enter image description here

A copy of the Workbook is HERE.

If you have any questions, just ask ;)

3
  • You sir are awesome. I hope you know that :) Everything works great. Just one question. Is there any way to do this without duplicating the values like in Row 2 and 3? – CuriousDev Aug 7 '16 at 14:00
  • 1
    @IWantToLearn the trick is, that you have 4 graphs which go from 0 to 1 without a change (this makes it a line). Without that you would only get dots. Still, you could do something similar with a "100% stacked bar chart", but you would need to alter the values depending on the "value". Main problem is, that the chart is transposed (different values below each other) and a normal (math) chart, can't have multiple y-values for one x-value (while having multiple x-values with the same y is ok). The scatter chart is different in this behavior. It allows to also draw lines in a not increasing order – Dirk Reichel Aug 7 '16 at 14:19
  • 1
    @IWantToLearn to show this, simply enter the numbers 0, -0.7, 1, -1, 0.7, 0 and below 1.1, 0, 0.7, 0.7, 0, 1.1. then select that range and hit in the insert-tab -> "scatter-chart" -> "scatter with straight lines". A normal mathematical chart would never allow to get a graph like this. However: we are using this here to "draw" a chart which looks like what you want. I often use it for triangle-calculations (for better showing what we do at a full scale triangle) ;) – Dirk Reichel Aug 7 '16 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.