Density of points on scatter graph in excel?

Microsoft Office 2010 home and student
March 15, 2011 at 00:47:48
Specs: Windows 7
Hey guys, just wondering if any of you could help me. Basically, im doing a paper in mathematics, and as part of it, have an iterative graph with about 21000 data points. Is there any way that one could, for every single point, count how many other points are within a fixed distance around it? Basically, im wondering if there is a way to count the density of points.
Thank you!

See More: Density of points on scatter graph in excel?

Report •

#1
March 15, 2011 at 01:45:11
You should be able to find a line through your data using an equation function.
I don't know if it will work with that many points. Using your data points you could find the number of near points to each point.

Report •

#2
March 15, 2011 at 02:11:56
Thanks for the suggestion, but it doesnt really have a fixed pattern as such, so i cant use an equation function. I think i should rephrase my question a little bit though. Basically, im look for a 3-d, iterative graph. One dimensions is X (n), the other is X (n+1), and the third dimension is the number of points within a circle of fixed size around that particular point

Im just not too familiar with visual basic. I just want a loop thats something like a = a+1 if: abs(distance between points) < 10 or something

Sorry, my question was a little vague


Report •

#3
March 15, 2011 at 04:22:40
I have another idea. Consider the data point as points of a x-y scatter chart then use the number of points near to the point as a z-value. This will give points in a surface. Equal z could be used to plot contour lines. I don't know how your points are ordered. I would sort by the x value then then y value keeping keeping the data pairs together. Then stepping through the points calculate and find the number of near points. I don't know what are the range of the data, so you might have to do this with different distances, I would use a flat file and store the results in another file that could be the input for plotting. A BASIC could do this quite rapidly.

Report •

Related Solutions

#4
March 16, 2011 at 02:26:21
The ordering of the pairs, and the two flat files really helped, thanks. But im not really too familiar with BASIC; how would one create a BASIC file that counts the number of near points for every single one of my 21000 points (i.e. distance is < 10), and input this count as a 'z' variable in the second flat file?
I really appreciate all this help you've given me so far

Report •

#5
March 16, 2011 at 05:16:59
For any point x,y the near points must have to have values within the range of x=x-10 to x+10 and y=y-10 to y+1+10 By stepping through the sorted list and testing the nearness of the point within the range you can accumulate the number of near points,

Sorry my explanation is a little convoluted.


Report •

#6
March 18, 2011 at 23:17:19
i understand what you mean, and its easy enough to do for one point. but I'm unable to do it by just dragging the black box for all 21000 points.

Report •

#7
March 19, 2011 at 05:10:30
By looping it is a not a too difficult problem, Could I inquire about the range of x and y values and are the difference integer values? I tried generating random values but the values were too small for a near value of 10.

Report •

#8
March 19, 2011 at 17:47:21
well the range for both is from 41.22 to 14164.53. It's honestly just the daily values of the dow jones from the last 70 years

Report •

Ask Question