How to make this one dimensional chart?

Hello,

I don't have Excel for Mac, but conceptually, you need data arranged as follows, then insert a horizontal column chart with a series for each subject (if you have more than one), add error bars with settings "Minus" and Percentage set to 100%, so the error bar goes all the way to the zero value. Then format the horizontal bar to have no fill and no border, so it effectively becomes invisible.

The scale on the image you posted looks funny. There is quite some distortion comparing 0 to 400 and 400 to 800, which I can't recreate with axis formatting.

2016-09-14_10-19-35.png

Perhaps they used another program. It is difficult to say from a picture. Excel just does not have the ability (built in) to handle non-linear axes like this, but, if you can "teach"/program Excel to do it, it can handle non-linear axes just fine.

If you are going to do it in an actual chart like teylyn began with, then you will need to apply an "aribtrary" or "dummy" axis technique. This reciprocal chart is an example: http://peltiertech.com/reciprocal-chart-axis-scale/ Jon has other examples, too (see "Axes - Custom Axis Scales using Dummy Series" here http://peltiertech.com/Excel/Charts/ChartIndex.html ).

I could also see doing this using an "in cell" charting technique. Basically choose an appropriate character (dash "-" maybe or n-dash or m-dash), then use the REPT() function to get an appropriate number of copies of that character based on your score. =REPT("-",int(score/10)) would give you one dash for every 10 units in your score. Of course, with the non-linear nature of your scale, you would need a more complex function to the number of characters. Once that is figured out, it would be easy enough to implement. (See this tutorial on in-cell charting: http://peltiertech.com/Excel/Charts/...ulaCharts.html ).

It is hard to tell from the picture exactly how they mapped their scale onto a linear scale. If I had to guess it looks like 0 to 400 maps onto 0 to 400, 400 to 452 maps onto 400 to 800, and 452 to 800 maps onto 800 to 1200 (or something similar).

locolindo,

maybe you don't need the width of basic, proficient and advanced to be exactly equal. It is quite misleading, actually to have the same distance for the first 400 units as for the next 52 units from 400 to 452, and then again the same distance for 348 units from 452 to 800. I consider this very bad data visualisation, because the human eye will judge the length of the line and try to relate it to the unit markers of the axis.

Could you just do this with some quirky maths on the back end?

Have your axis go to, say, 300, with the dividers on the hundred marks. Then put some logic for the 3 parts that give an accurate bar size.

=IF(C16<400,C16/4,IF(C16<452,((C16-400)/52*100)+100,((C16-452)/347*100)+200))

This formula takes the 3 sets of values (0-400, 400-452 & 452-800) and changes them to 0-100, 100-200 & 200-300.

If you can get the chart to then use fake axis values (or just remove the axis legend and put the numbers in a cell underneath or something?) then it should look right, even though it's kinda fudged.

cap5.PNG