automate chart and format data point colors

Hello,

I am having trouble trying to automate the following graph (see attachment). Basically, there is a graph with mean scores plotted on it. I have a table of the mean score for each participant that completed the survey. Currently I manually plot each one on a graph.

What I want to do is have the score automatically plotted on a x-axis graph - with duplicate scores (x's) placed above each other. I have tried to create a chart doing this, but am unable to get it to look similar to how the graph was created manually (the color of the data points to be consistent with manual layout AND automating the y-axis calculations).

Any suggestions??

Thanks in advance!
Sarah

The calculation of the Y position is not too difficult and the attached has formula to do this.
The real problem is creating the stacked effect so that the x's look like they are stacked on one another rather than floating.

With the xy-scatter chart you can adjust the Y maximum value to compressed the data but this will require a manually process.

I also tried using a column chart with stacked picture fill. This also has a similar floating x's problem.

If you forget about the x's you can use the column chart to do all the work you need.

Thank you Andy!! I will take a look and see how I can incorporate it into our reporting.

Sarah