Excel spreadsheet Multiple Y values versus one X value
I have multiple columns representing Y values each against a specific x value. I do scatter. When plotting each series, I can easily select the y values as they are present in the columns, but the x value is constant for each column and I cannot figure out how to repeat the constant x value against multiple y column values. Does anyone know how to provide a single repeatable value in the "X-Values" textbox?
Arrange your data as follows, then select the range of cells containing ALL Y values and from the ribbon, enter Insert Chart | Scatter of points.
Is this what you expect? Each series in this chart represents one column of data. X-Values are not repeated and do not need to be repeated.
You may need to slightly adjust the range of values, you can do this by grabbing the blue / purple outline of the range and resizing it, for example:
If you don't like the default labels / numeric X-axes, first create the chart as a line chart, and then format the series so that it is No Line, the result should look like a scatter chart, except for using category labels:
a source to share
If I understand your question correctly, you should probably change your data by one series so that you have 2 columns and many rows. Column A stores the x values, column B stores the corresponding y values.
So from this:
A B C D ...
1 10 13 16 17
2 11 14 18
3 12
:
A B C D ...
1 xa 10
2 xa 11
3 xa 12
4 xb 13
5 xb 14
6 xc 16
7 xd 17
8 xd 18
a source to share
I have no idea, still need to figure out how to do this, but I had the same problem and the first answer here inspired me to try something ... and it worked. Basically you just iterate over the xn value for the corresponding y values in a separate column. Say your x values are 2.5, 5, 7.5 and 10 and your y values are 1,2,3,4,5,6,7,8 (2 y for each x), in column A - 2.5, 2.5, 5, 5, 7.5, 7.5, 10, 10, and in column B - 1,2,3,4,5,6,7,8. Hope this helps.
Let's assume you have data like this:
A B ...
1 3 2
2 5
3 7
You can create a named formula in Formulas -> name manager (let's call it ConstA1
)
=Sheet1!$A$1*ROW(Sheet1!$B$1:$B$3)/ROW(Sheet1!$B$1:$B$3)
and refer to it in the "X series values" field for the series in the scatterplot
=Book1!ConstA1
when plotting =Sheet1!$B$1:$B$3
along the y-axis. Also consider using data tables.
Inspiration: link
a source to share