X-Axis plotting a series, not values, on scatter chart

[strap426]

I am trying to create a scatter plot with two different series on it. (Both can use the same y-axis). The x-axis data is the same for both series. The Y-axis data is in two different columns. Like this:


[TABLE="width: 200"]
<tbody>[TR]
[TD="align: center"]x[/TD]
[TD="align: center"]y1[/TD]
[TD="align: center"]y2[/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]250[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]1000[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5000[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

​

Instead of getting the first two x-values kinda close together with the fourth x-value much further out, I'm getting perfectly evenly spaced x-values labeled 1, 2, 3, and 4.

The values are all links to other spots in the spreadsheet. But it happens even when using raw numbers. I have tried everything I can think of. This is driving me crazy!! Please help!

Thank you in advance. --Ben

 

[JoeMo]

Welcome to the board.
Remove the x as below, then select all the data and headers >Insert>Scatter Plot. You can put the x back after you generate the plot.
Sheet5

*	A	B	C

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]y1[/TD]
[TD="align: center"]y2[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]5000[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]8[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[strap426]

Thank you much JoeMo!

In the x-column, in each row, I have this formula: if(isblank(h9),"",h9)
So some of my x-values are "", which causes the x-axis data to be a series.

Your information made me realize I can't use that formula.... So you helped me solve my problem.

If there IS a way to use that formula, please let me know! Otherwise, I can adapt. Thanks again!!

 

[JoeMo]

What cell is the header "x" in?

 

[strap426]

Here is what my actual, real data looks like:

[TABLE="width: 250"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Polymer 1[/TD]
[TD="align: center"]Viscosity[/TD]
[TD="align: center"]SF[/TD]
[/TR]
[TR]
[TD="align: center"]50[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]500[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]5000[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"] [/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]6[/TD]
[/TR]
</tbody>[/TABLE]


The Polymer 1 column are my x-values. The empty cells are "", the result of the formula. Every one of those cells is a link to another place in the sheet.

I'd love to have the plot encompass all 7 rows but plot nothing where there is no x-value.

 

[strap426]

I could use conditional formatting to white out the cells and leave them blank (or zeroes), but then I'd have a plot with zero values in those cells. Right?

 

[JoeMo]

As you know Excel works with cell addresses so you need tp provide some layout info. What cell is the header Polymer 1 in? Are the Y values in the next two columns to the right of the x values? Zero values for x will produce data points on the y-axis for a scatter plot assuming the plot is set up conventionally with the Y-axis crossing the x-axis at x = 0.

 

[strap426]

My apologies for the late reply. I was caught up in an unexpected project at work. I really DO appreciate any and all help I get here!!!

I was previously trying to simplify the data because the real data is quite complicated, hinging on cells in several other tabs and worksheets.
However, I think the best way to get the help is to show you a screen shot of the plot I'm trying to create.

I'd like two lines on this scatter plot: one for viscosity and one for screen factors. The screen factors y-values are in column BF. The viscosity y-values are in column BG. The shared x-values are in column BE.

As you can see, they are all links. And their source cells are also either links or calculations. I'm hoping Excel can accommodate....?

https://www.dropbox.com/sh/jjcg9hs1ckfvd2g/K46_K9o--6

 

[JoeMo]

The fact that your x and dual-y values are calculated has no bearing on chart creation. Remove the "x" in BE8 then select BE8:BO15 > Insert > Scatter Plot. Once you have the plot you can put the "x" back in BE8 if you like.