How do I write an excel macro that will calculate the cumulative distance between points

Question

I am trying to write an Excel macro that will calculate the cumulative distance between points based on their x and y coordinates. Here is an example of my current worksheet set-up:

Point #       Code     x coordinate    y coordinate   Cumulative distance between points

 1            1           39.4             99.8                       0
 2            1           42.5             104.5                      ?
 3            1           43.0             105.8                      ?   
 4            2           34.0             150.5                      ?
 5            3           38.9             145.9                      ?
 6            1           46.2             103.4                      ?
 7            1           48.5             105.3                      ?
 8            4           67.6             98.5                       ?
 9            1           50.3             103.2                      ?

I need the "cumulative distance between points" column to calculate the distance between the the point and previous point using Pythagorean formula =SQRT(((D2-D1)^2)+((C2-C1)^2)))). For each subsequent value in the "cumulative distance between points" column the value must calculate the distance between the previous points and add a running total of all distances calculated.

Here's the kicker. I only want the distance between the two points to be calculated if they both have a code of "1". If you look at my example you can see I have codes with values between 1-4.

In the above example, therefore, I would want to calculative the distance between point # 2 and 1. Next, I would calculate the distance between point # 3 and 2, and add it to the value obtained from the previous cell in the column (=distance between point # 2 and 1). Next I would skip points 4 and 5 as they do not have codes = "1". The next calculation would be between point # 6 and 3, as they both have codes = "1". Point 8 would be skipped as the code = "4". Then calculate the distance between point # 9 and 7. Keep in mind that every time a value is calculated in the "cumulative distance between points" column it must be a cumulative value that adds all the previous values to it.

So to wrap it up, I need the following:

-In the "cumulative distance between points" column calculate the distance between points (using x, y coordinates) when both codes = 1. -add the previous cumulative distances to the cell value to get a running total of distance.

I hope that is clear, if not I can fill in any blanks that are confusing.

Answer 1

You could do this on the worksheet albeit with a few extra columns.

1) Create two new columns "copied x" and "copied y". Put formulas in these columns that, if the code on a row is 1, copies the x and y columns on that row. For any other code, copy the copied x and y values from the previous row. Use the Excel IF function to do this.

2) Calculate the cumulative pythagorean differences on all the successive "copied x" and "copied y". It will add a zero contribution for any row with a code other than 1. This is the final column that you add.