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

misterj0shua

New Member
Joined
Jan 17, 2022
Messages
4
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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.

So let me start with what I have learned so far:

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
    Ecel Question.png
    36 KB · Views: 7

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
2,229
Office Version
  1. 365
Platform
  1. Windows
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.

1651198158022.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
5,258
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

SortChartData.png


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.
 
Solution

Forum statistics

Threads
1,176,119
Messages
5,901,483
Members
434,896
Latest member
Derquila

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top