Hi forum,

I am pretty inexperienced with advanced Excel and would like some guidance on tackling a data/calculation management problem. I have a pretty good knowledge of basic functions and formulas and I have successfully created a spreadsheet to perform my desired calculations for ~200 data points (described later). However, the problem I am encountering is that when I try to increase the number of points to 500, the process grinds to an unbearably slow pace (2-3 minutes to open or save the file).

Basically what I am trying to do is take an array of points (each with an x and y coordinate) and compare each point to every other point to determine the distance and angle between them and then a number of additional calculations. Furthermore, I need the ability to turn points on and off, and the purpose of the point comparisons is to determine which points to enable. Currently, my design looks like this:

Point 1 x&y
-P2 x&y
-P3 x&y
-P4 x&y
...
Pn x&y
Point 2 x&y
-P1 x&y
-P3 x&y
...and so on

Thus, for any number of points, I will have n^2 rows and ~20 columns of calculations.

Currently my desgin compares every point to every other point whether the points are turned on or not. What I'd like to be able to do is compare every point to ever enabled point which would significantly cut down the number of calculations needed.

Thus, if points 2 and 4 are enabled I would like to do:

P1 x&y
-P2 x&y
-P4 x&y
P3 x&y
-P2 x&y
-P4 x&y
etc..

This adjustment would need to be dynamic as the user will be enabling and disabling points all the time.

Another possibility is that instead of actually adding or subtracting rows of calculations, I was wondering if perhaps there was an IF statement that I could use to tell Excel not to do any calculations on a given row. In other words I'd like to be able to say if this point is turned off, don't do any of the calculations on this row.

A push in the right direction would be helpful.

Thanks in advance!
Andy