Plot a value on the chart based on three values

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 -

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

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:

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

Do not get confused, you will get a chart which looks like this:

Now we right click the chart and hit "Select Data..."

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

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:

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).

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:

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):

A copy of the Workbook is HERE.

If you have any questions, just ask ;)

• 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
• @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
• @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