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:

x	y1	y2
100	2	2
250	2.5	4
1000	3	6
5000	3.5	8

<tbody>
</tbody>

​

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
1		y1	y2
2	100	2	2
3	250	2.5	4
4	1000	3	6
5	5000	3.5	8

<tbody>
</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:

Polymer 1	Viscosity	SF
50	4	6
100	8	6
500	12	6
5000	16	6
	20	6
	24	6
	28	6

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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.