# Scatter Graph and connecting points based on location in graph instead of

#### misterj0shua

##### New Member
Please bear with me as I haven't taken a geometry class in a while and I have done a ton of research, but can't find the exact answer to my question. I found a solution, but its not the best solution, so I want to make sure there isn't another one.

I have 4 points I want to graph. Each point has 2 values for example: (21 100) (34 112) (42 120) (58 110)
9.6 107.8
11.4 108.8
13.9 111.3
14.3 109.9

Now these values I am trying to graph on a scatter graph with a smooth line and it would look something like a bell curve with the first numbers (21-58) on the X axis and the second number (100-120) on the Y axis.

The problem is when these numbers are entered they may be entered out of order. So using the example above

9.6 107.8
11.4 108.8
14.3 109.9
13.9 111.3

now when I plot these on the scatter graph with a smooth line the graph connects the dots as entered. So now instead of a nice curve I have a goofy curve, because instead of connecting points 1,2,4, and 3 in a nice line it goes 1,2,3,4.

I understand the relation these numbers have to each other as the left column will always continue to go up and the right column as some point will reach a max and then drop off (120 -----> 110).

I also understand that I could sort the left column, but the numbers in the right column would have to follow the numbers on the left as they are related. However, since this is an automated process I would have to use a formula to do it and I am currently looking into that.

So I am wondering if there is an option in the graph that just connects the points together left to right without worrying about if its point 1 or 3. Its just whatever is closest it gets connected too. So in the example above it would connect points 1,2,4,3. I hope that makes sense. So you plot the 4 points and then just connect them with a line.

If I am unclear about what I need please ask. I posted a picture which i hope helps clarify. Basically how do i make the bottom chart look like the top chart with the data points entered as is.

#### Attachments

• Ecel Question.png
36 KB · Views: 19

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I'm not aware of a such an option in Excel, but if you're using Excel 365, conditioning the data is straightforward. Let the incoming data continue to come into the two columns (say A:B) and then use a SORT function on those columns to reorder the x values in ascending order (the corresponding y values will follow along with them).
MrExcel_20220426.xlsm
ABCDE
19.6107.89.6107.8
211.4108.811.4108.8
314.3109.913.9111.3
413.9111.314.3109.9
5
6
7
8
9
10
11
12
13
14
15
16
179.6107.89.6107.8
1811.4108.811.4108.8
1914.3109.913.9111.3
2013.9111.314.3109.9
Sheet7
Cell Formulas
RangeFormula
D1:E4,D17:E20D1=SORT(A1:B4)
Dynamic array formulas.

The chart's data has to be in order. But it's not too big a deal. You could sort it manually, then plot it. Or you could use one of the new Microsoft 365 functions to sort it.

The original data is in A2:B5, and is plotted in the first chart.

The formula in A11 is =
Excel Formula:
``SORT(A2:B5,1)``
, which means sort A2:B5 by the first column. It is plotted in the second chart.

I will marked this Solved. Thanks for the reply!

Replies
3
Views
130
Replies
1
Views
494
Replies
1
Views
955
Replies
6
Views
936
Replies
3
Views
3K

1,216,095
Messages
6,128,792
Members
449,468
Latest member
AGreen17

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back