distance and length of points along the line

Hello everyone,
We are trying to find the length and distance of perpendiculer point from the line using X Y coordinates. I already have an excel file that can solve the problem but it is a difficult one. It can only know one line from coordinates. This is another way to find out from several coordinates of the line.

Thank you

You have only two numbers that specify the line. That is not enough information, unless there is an un-known assumption.
Can we assume that the line passes through both the origin (0,0) and the point indicated?

This looks like the same question posted earlier this week. I posted a response to the previous thread, with no response. In my response, I outlined a basic approach, but I am not sure if it will be appropriate for your specific scenario, or if there are details I am not understanding. What did you think of my previous response? Any comments or questions about delving into the programming details?

Copy of my previous response: I'm sure there is a solution -- it is more about understanding your exact requirements and figuring out the desired algorithm.

Like Pete, I am not sure I understand what is different about this question and the previous one. Can you elaborate? Considering the success of karedog's solution in the other thread, it might be easier to start with his VBA code and adapt it to the new requirements for this question rather than start from scratch.

It also might be important to know if, like the other thread, you are trying to replicate an AutoCad calculation -- just in case there is something about the AutoCad method that needs to be taken into account.

If I were starting from scratch on this question, it looks like the question should reduce down to the "distance from a point to a line defined by by two points" formula given by Wikipedia: https://en.wikipedia.org/wiki/Distan..._by_two_points I expect that, if I were starting from scratch, I would start there. The main complication I see beyond the basic formula is that your problem needs to decide which line segment to use. I'm not sure, but I expect that I might use some kind of trial and error algorithm for that.

Before spending a lot of time exploring this, can you clarify your requirements? Do you understand karedog's solution in the other thread to suggest how it needs to be modified for these requirements? Are there specific parts of this you need help with, or, like with the other thread, do you need someone to develop the entire solution for you?

Link to previous thread: https://www.excelforum.com/excel-for...oordinate.html

MrShorty,
Yes Sir, I have calculated from AutoCAD.
My mathamatic is not fine.That's the reason I did not reply. Line coordinate showing in "test" file,*The first coordinate of the line is calculated as zero length (Start point). The number of coordinates will increase, the line will increase that the length of line.*and the point are along the line using of coordinate.*We will calculate every point from the zero length of the line to the perpendicular length*As well as the calculate*perpendiculer distance from the line.

mikerickson,
You see attached "test.xlsx" files.
The currently attached image is for example.

sanju2323, this is the third thread (one, two) you have opened on the same issue. Please continue in this thread until you get a solution, and do not continue to open new threads.

My mathamatic is not fine.

I often find that I must understand the mathematics of a problem before I can hope to understand the programming of a problem. After puzzling over this some, it occurred to me that the problem is basically vector addition, so I might suggest that yous tart by reviewing the basics of vector addition:
http://mathworld.wolfram.com/VectorAddition.html
https://www.onlinemathlearning.com/vector-addition.html
https://en.wikipedia.org/wiki/Vector...nd_subtraction

With basic principles reviewed, the basic problem is simple vector addition. Starting from a given point (x0,y0), you are adding two vectors (r1,theta1) and (r2,theta2) [r is the length of the specified vector and theta is the angle of the vector] to arrive at a third point (x1,y1). The basic addition can be written as:
x1=x0+r1*cos(theta1)+r2*cos(theta2)
y1=y0+r1*sin(theta1)+r2*sin(theta2)

We need to recognize that theta1 is the direction of the specified line segment given in columns A:C. From what I can tell, theta2 is an angle perpendicular to theta1, turning right, so theta2=theta1-pi()/2

In your first thread, the problem was given r1 and r2, find x1 and y1. Two equations in two unknowns -> solve the system of equations, which is fairly straightforward. Karedog noted some additional information about how AutoCad performs this calculation that seems unusual. We would need to fully understand what AutoCad is doing that is different from this simple description in order to completely replicate the AutoCad calculation (or decide that we don't need to exactly mimic AutoCad).

In this thread, the problem is, given x1 and y1, find r1 and r2. Still two equations in two unknowns -> solve the system of equations, but the solutions are a little more complex. If you need a refresher, now would be a good time to review basic "solve a system of equations" strategies: https://www.purplemath.com/modules/systlin1.htm

That should be the basic geometry problem that this represents.

MrShorty, I have searched PDF file This pdf has the information regarding offset (Distance) and Chainage (Length) calculation.
http://zasoby.open.agh.edu.pl/~11sjjurek/domiaryen.html

Both of those still look like they are using basic vector addition approaches to the problem.

The zasoby link is very similar to the approach I proposed. Some of the notation is different, but otherwise very similar. It would still essentially be a "solve a system of equations" approach.

The Casio calculator program was intriguing. The programmer there used complex numbers to represent the vectors, which seemed to simplify the calculations. Excel has a good collection of functions to work with complex numbers -- I wonder if this would be easier if we treated everything as complex numbers?

Having established that there are a few different ways to approach the problem, which approach do you want to use?

MrShorty,
Sorry sir, but I'm still not sure. Because I do not know which link will give correct calculation But I'm still sure that the attached is an excel file, it gives the correct answer.
I can show a small changes in the excel file by which a few points will be answered correctly.

Because I do not know which link will give correct calculation

As near as I can tell, any of them should give the correct calculation. For that matter, your calculations in your two files here look correct. The only thing "wrong" about the calculations are that the calculations all reference the first SL segment rather than the nearest segment. I hate working on single cell mega-formulas, so I made the following simple edits to Length and distance 2.xlsx:

1) In column P:S, I want a way to easily change the SL segment being referenced.
1a) In O8 I enter "SL". In O9, I enter 1.
1b) In P9, I enter a lookup formula to return the X value for the SL segment identified in O9. =VLOOKUP($O9,$B$9:$E$508,COLUMN(B$1),TRUE). Copy into Q9 to get the Y value
1c) In R9, I enter a lookup formula to return the X value for the end point of the SL segment. =VLOOKUP($O9+1,$B$9:$E$508,COLUMN(B$1),TRUE). Copy into S9 to get the Y value
2) I quickly discovered that one of the things I had not understood is that the "length" calculation represents a cumulative length along the blue line beginning at SL 1. In order to obtain this value, I added a cumulative length column in column E to include in my lookup table. In E8 I enter "Cumulative length". In E9, I enter 0. In E10, I enter a cumulative distance formula =E9+SQRT(SUMXMY2(B10:C10,B9:C9)). Copy/fill down to the bottom of the table.
3) I need to add the cumulative length to my lookup results in O:S, so I insert a column between S and T and add the lookup function in the new T9 =VLOOKUP($O9,$B$9:$E$508,4,TRUE).
4) Select O9:T9 and copy/paste/fill down to the bottom.
5) Add the cumulative length to the lengths in the final result cells in column I. I9 =IF(G9<>"",X9+T9,"") Copy/paste/fill down.
6) Now I can go down and enter the appropriate SL number for each row in column O until the calculated values in I:J match the expected values in L:M. At this point, I do it manually, which is tedious but easy.

I can mostly match the values, except in cases like row 17, where we encounter some of AutoCad's special calculation. You have not stated if it is necessary to replicate AutoCad in these cases, so I have not attempted to understand or replicate what AutoCad does in these cases.

Step 6 could be automated, if one fully understood the decision criteria for choosing the correct SL number. Some of it I can easily understand, but there are a few points that I don't see how you (or AutoCad) chose the specific SL number. For now, I would do it manually, until I fully understood the trial and error process and decision criteria I would use to choose SL number.

It might be until the weekend before I can upload a sample file. Which formula seems to be off or which step did you have trouble with?

Difficult to debug "the answer did not match" without some details. Until I can upload my file, can you upload your attempt to follow my instructions, and I can look at it and see if I can see where you went wrong?

Sometimes I dislike giving detailed solutions, because it seems that people merely copy what I did without really understanding it. Be sure you understand each step well enough to not only copy the formulas into your spreadsheet, but so you can understand what it is supposed to do and debug errors (whether mine or yours).

When I first open this file, I see in columns Q and R (the X2 and Y2 columns) that both values are the same all the way down. Looking at the lookup table, I see that the formulas in Q and R are both returning the X value for the second point and not the Y value. Looking over in columns O and P, I see the same mistake (except in row 9). I copy Q9 and paste into R9 to correct the mistake in R9. Then I select O9:R9 -> Copy -> Paste/Fill down. Then I spot check several rows in O:R and make sure they are returning the correct X1, Y1, X2, Y2 values for the SL number specified in column N. If any of the X,Y pairs looks incorrect, debug why the lookups are not returning the correct values.

Assuming we now have the lookups correct in O:S, I look at the results in I9:J9 and compare to K9:L9. I notice that J9 and L9 match, but I9 and K9 are very different. I check the formula in I9 and see that it is summing up the values in T9 and X9 -- just as I posted in step 5. I notice that T9 and X9 are no longer the "cumulative length" and "length" values as they were in the previous file. It appears that you deleted the previously empty column K in this new file, but did not adjust the references in this formula to account for the left-shift in the columns. I correct the formula in I9 to sum S9+W9 and copy I9 down. I9 now matches K9, as expected.

Now I go through the process of selecting SL numbers in what is now column N. I enter 1 in N10 and I10:L10 match up. I enter 1 in N11 and I11:L11 match up. I enter 1 in N12, and I11:L11 do not match so I try 2 in N12. J11 and L11 now match, but something is off in I11. The references in I11 are correct, so I follow the sequence of calculations back to E10 -- which looks wrong. I check the references and see that it is referencing B10:C10 and B9:C9 (just as I gave in step 2). I'm not sure why I made that mistake (probably thrown off a column by the empty column A), but it should be obvious that SL number has nothing to do with a distance formula. A distance formula will refer to the X and Y values of each point. I correct the references in E10 so that they are referencing C10:D10 and C9:D9. Then copy E10 and paste/fill down. Now I12:L12 match.

I continue entering SL numbers in column N until most of the rows match up (N13:N15 are 4, N16 is 5, N17 is one where they don't seem to match up but I guess a 6, N18 is 6, N19 is 7, and so on). In most cases, I can find an SL number to enter in column N that will cause I:K to match.

That should not only explain the edits I made to the latest file, but also walk you through the debugging process I followed to show how I identified where the errors were. Make sure you understand each step of the process well enough to correct any further mistakes along the way.

Here is a file with the corrections described in post #20. The corrections seem simple, so I am curious what difficulties you were having with these corrections. I added some comments to the original file where I could see the errors. In the copied tab ("Length and Distance (2)"), I added additional comments highlighting the changes I made. I also noted in column Y where I entered appropriate values for SL number in column N, the mismatched value in row 17, and indicated which row you will need to continue entering SL numbers in column N.

Thank you for the uploading excel,
The cell no. "I17" is incorrect because of this, if there is a point in the nearest place where a line turns, then AutoCAD shows two imaginary perpendicular points, If seen both are correct, but we will calculate the perpendicular from those points where the lines turn.
This is explained detail in the previous thread.
https://www.excelforum.com/excel-pro...-distance.html

Right. I did not spend a lot of time with that aspect because you never indicated in this thread if you needed to replicate the AutoCad calculation exactly or not. Do you fully understand the AutoCad calculation (you seem to understand it better than I do)? It seems like it should just be an additional IF() condition in the formula in column I -- =IF(G9<>"",IF(is this point close to node,close to node calculation,S9+W9),""). Column J will need a similar IF() test =IF(is this point close to node, close to node calculation,SIN(T9-U9)*V9). Can you fill in the condition and the calculation? I'm afraid I did not understand the details that Karedog was able to understand, but if you understand them, it should be a simple matter of filling in the arguments of the IF() functions.

As I explained in post #14, yes, the process of choosing the number in column N can be automated. I expect it will need to be a suitable "trial and error" process. At this stage in programming, I would manually determine which SL number is supposed to go with each point until I have a good idea how that trial and error process works, how I will decide when I have found the correct SL number, and what ways I can find the wrong SL number. Once I understand the trial and error algorithm I want to use, then I can work on programming that algorithm into Excel. I have not yet thought through this trial and error process in any detail (other than just guessing until I find the one that causes I:J to be the same as K:L). As you have entered different values in column N, how have you chosen which SL number to enter?

So you start with the length of the line (which line?). Then you can calculate the serial (is serial SL number?). How do you calculate serial from the length of the starting line? How does the length of the point in between figure into the calculation?

Right, you won't know the length before starting the algorithm. You won't know the length until you choose an SL number.

If it helps, here's a quick overview of how I have chosen values in column N.

1) I observe that the points in F:G are in order starting from SL 1, so the first point can only refer to SL 1. Enter 1 into N9. Move on to point 2
2) Because the points are in order, point 2 cannot refer to the SL number before point 1, so I start by trying SL 1 in N10.
3) Test results to see if 1 in N10 is the correct result. Up to now, this has been a simple "does I:J match K:L", but I expect that you are needing to develop a test that will not reference K:L. I am not sure what this test criteria looks like. What would you use?
4a) If result of test in (3) suggests that the value in N10 is not the correct SL number, then go back to step 2 and increase value in N by 1. Repeat steps 2:4 until the correct SL number is found.
4b) If the result of in (3) suggests that the value in N10 is the correct SL number, then move on to the next point. Continue repeating steps 2:4 for each point in F:G.

That mostly seems to work. The main questions are whether we can assume that the points in F:G will always be in order from start to finish along line, and what the test criteria in step 3 should be. When you decide which SL number to use, how do you decide?

It looks to me like Karedog's solution is basically the same one you are using. He builds a "column" inside of the VBA array that contains the cumulative lengths (should be equivalent to columns A and E in our sheet), then takes the given "length" value and performs a "brute force" linear lookup on that "column". We can perform the exact same lookup as you show in column M (and our use of the built in VLOOKUP() function can use a more efficient binary search algorithm rather than a linear search algorithm). Karedog's lookup solution works well for that problem, because he is given the length value in the problem statement. Once you have added a column of cumulative lengths to the original SL table, it is a simple task to lookup and find the corresponding SL number from the given length. Your column M in L&D #6 shows this -- given the length value in column K, it is a simple matter to find the SL number.

Unless I am misunderstanding something, in our case, we are not given the length value -- we are trying to find the length value. (Unless I am wrong -- if column K is going to be given for this problem, say so, because having K among the givens (along with F and G) simplifies the problem a lot.) Assuming K is not given, then I still think you are going to need some kind of trial and error algorithm to determine the SL number for each point.

Let's stop there until you can say for sure if K is among the given information or if it is a value to be found.

It seems like we should be able to figure it out. Have you looked over the procedure I outlined in post #31? Have you manually tried it a few times? Have you considered what to use for the test criteria in step 3? I don't expect that programming the trial and error algorithm will be overly difficult, once we understand what series of tests to determine when we have found the correct SL number.

I have found a vba function after searching on the internet, it can find distance from line and point coordinates. But it also uses fix two coordinators of the line.
https://answers.microsoft.com/en-us/...d-fd996a4e8218

https://en.m.wikipedia.org/wiki/Dist...oint_to_a_line

That function shows yet another way to approach the basic geometry of the problem. I don't know if using a UDF to get the the offset distance will be substantially different than the sequence of build in worksheet functions that we were using. As you have it now, it still has the same problem we had before (before post #14) in that the calculation references only the first SL number and has no way of choosing which SL number to use for the actual calculation. If you strongly prefer using this (or any other UDF) we can go back to post #14 and figure out how to add lookups and the other calculations to the UDF, but we will still end up in the same spot -- how to correctly choose the SL number that a specific calculation should reference.

Here's one observation I recently made (using the L&D 6 file from post #32). The length returned in I should be between the cumulative lengths of the two points used to define the SL segment in O:R. For example, the length returned for the first point (I9=0.977) is between 0 (E9 the length up to SL 1) and 41.4 (E10 the length up to SL 2). However, when I get to row 12, if I put 1 in N12, the returned length in I12 is 51.1, which is not between 0 and 41.4. So I try 2 in N12, which returns 42.5 in I12 which is between 41.4 (E10) and 54.0 (E11). If I continue and try 3 in N12, I12 returns 39.8 which is not between 54.0 and 69.6. If I am confident that the single criteria is sufficient, then I conclude that 2 is the correct SL number for row 12 (comparing to K12:L12 confirms that this is correct).

Continuing on to row 13, I enter 2 in N13 and I13 returns 80.9, which is not between 41.4 and 54.0. So I try 3 in N13 and I13 returns 80.2, which is not between 54.0 and 69.6. So I try 4 in N13 and I13 returns 77.7, which is between 69.6 and 117.1. For good measure, I try 5 in N13, which returns 76.8 in I13, which is not between 117.1 and 149.9. So I conclude that 4 is the correct SL number for row 13 (comparing to K13:L13 confirms that this is correct).

You might continue with that manual process and see how it works for all entries and see if you can learn more about this process.

The pdf seems to show more geometry -- again illustrating that there are different ways to visualize the geometry of the basic "find the distance from a green point to a purple line segment" problem. Unless you have more questions about that aspect of the geometry, it seems that we know pretty well how to handle that aspect of the geometry of the problem.

When I look at the diagram in the pdf, it is intuitively obvious to me that I am trying to find the distance from green 2 to the purple 1-2 segment. I look at the green 5 point, and it is intuitively obvious that I am not trying to find the distance to the purple 1-2 segment, but that I want to find the distance to the purple 4-5 segment. As I understand the problem to this point, the programming difficulty is how to get our computer program (whether spreadsheet formulas or VBA) to make the same choice that seems obvious to you and me.

"Closest" seems like a part of the decision, so maybe an algorithm that:

1) Build an array of distances (and angles while I'm at it) for the green point to every purple point.
2) Determine the smallest distance in that array and identify which purple point it belongs to. This seems like it should be one of the endpoints of the purple segment I want to choose.
3) Calculate angle and distance to both purple segments with that purple point as an endpoint.
4) At this point, I should have three results (angle and distance to the closest point, angle and distance to the segment before the closest point, and angle and distance to the segment after the closest point), and I choose from those three which is the correct solution.

Does it seems like that algorithm should work? Can you find any points in your list for which this algorithm fails to find the correct result?

I did some testing, and it looks like that should work. Roughly programmed in, here's how I did it:

1) As noted, step 1 is to create an array of distances to each point.
1a) Copy the SL numbers in B9 to B59, select AC1, paste special -> transpose to get a horizontal copy of the SL numbers.
1b) In AC9, I enter a distance formula =SQRT(SUMXMY2($H9:$I9,INDEX($C$9:$D$59,AC$1,0))). Note the mix of relative and absolute references for ease of copying. Also note how I used 0 in the "column #" argument of the INDEX() function to get the entire specified row (help file for the INDEX() function that explains this behavior https://support.office.com/en-us/art...2-b56b061328bd ). Copy AC9 across to CA9.
2) Step 2 is to find the closest point, which is a simple MIN() function. In CB9, I enter =MIN(AC9:CA9). To find which point that distance belongs to, I use a simple MATCH() function. In CC9, I enter =MATCH(CB9,AC9:CA9,0).

Select that row of cells and copy down.

Comparing the results in step 2 to the values in columns P and Q suggests that it is pretty close on all points. It should then be a matter of copying the calculations in columns R:AA and performing them twice -- once for the number returned in CC and once for the number before the value in CC. Then determine which of those two calculations is the correct one or if the result needs to be the distance to the SL number.

Have you had a chance to look at it? Does it seem to work?

I have an answer like a visual effect in my mind but I can not explain it.

It sometimes seems like that is a big part of programming sometimes. How do I translate this vague idea in my head into a sequence of instructions that a computer can follow.

If you try to make a triangle with a line and a single point, then getting the answer will be easy.

And we already have several approaches to solving this problem. The problem we have now is that we have more than one triangle. We have a point and line 1-2 that form a triangle, and the same point with line 2-3 that forms a different triangle, and so on to the point and line 49-50. Solving the individual triangles is relatively easy at this point. The challenge is choosing which of the many triangles is the correct triangle. Like I said, the "closest" triangle seems an intuitive choice, but we need to think carefully through how to choose the "closest".

A couple of comments and questions on this new triangle method:

1) I notice that your formulas in Z:AA are always referring to the points in P9:R9, instead of referring the current row (Z11 refers to P9 and Q9 instead of P11 and Q11). As noted much earlier in this, if I understand what we are trying to do, each row needs a way to reference a different segment and cannot all reference the same segment. I would expect each row's formulas to reference the other cells in that row.
2) I notice that your formula in column AB is always referencing A10 -- which is correct for the first segment, but will not be correct when one needs to reference other segments. I would use a lookup formula here like in our other implementations. If you are not yet comfortable with Excel's lookup formulas (we have used both VLOOKUP() and INDEX(...,MATCH()), then I recommend you spend some time with those lookup formulas so you understand how they work.
3) A simple triangle based approach like this cannot tell "direction", so all computations are positive. Your target values show some results as positive and some as negative, which indicates direction (some are on the "right" side of the line and others are on the "left" side of the line). One of the advantages of the vector addition based approaches we have been using is that these approaches inherently "know" which side of the line they are on. The simple triangle approach does not. Additional calculations could be added to determine which side the point is on and assign the appropriate sign to the result, but that seems more difficult than simply using the vector based approaches we have been using.
4) This triangle based approach still does not tell us how to choose which segment to reference in the calculations. I still think the vague notion of "closest" is the idea, but I have not worked out the programming details for identifying "closest". Do you have any thoughts or insights into choosing the correct segment?

I would be inclined to stick with the vector based approaches that we have been using, because they have both distance and direction built into them. But, if you prefer this new triangle based approach, I'm sure we can figure out the formulas to detect direction and add those to the method. Which approach do you prefer? Any thoughts on choosing the SL number to use for each row's calculations?

I tried the algorithm I described in post #43 and it mostly succeeded -- except for row 94. The algorithm looks like it would have worked for 94 if I had included the 3 or 4 closest segments instead of just the 2 closest. Programming this in the spreadsheet was fairly easy. You already have the closest point identified in columns CF and CG. I copied the calculations in column P:Y over to CH (adjusting references as needed) and made the computation for the closest SL number. Then made another copy of the calculations (again, adjusting references as needed) and performed the same calculation for the previous SL number. By comparing the length (should be between start length for SL number and end length for SL number) and distance (smaller of the two distances if both were possible) for the 2 segments, I could choose the correct segment in almost all cases (as noted, only row 94 failed). I think if you expanded the algorithm from the 2 closest to 3 or 4 or 5 closest, the algorithm would succeed for every case.

I have not given the formula details, because they are just copies of the formulas you created in U:Y with the simple lookup formulas in O:T. Let us know if you have trouble with copying those formulas to the columns to the right of the distance array(s).

Originally Posted by MrShorty

I would be inclined to stick with the vector based approaches that we have been using, because they have both distance and direction built into them. But, if you prefer this new triangle based approach, I'm sure we can figure out the formulas to detect direction and add those to the method. Which approach do you prefer? Any thoughts on choosing the SL number to use for each row's calculations?

I would also like to go to Vector. I had given triangle formula for this reason, because I felt that if we can trace the distance from triangle in the same way, it can also be found in the length too and the length only comes in positive values.

Originally Posted by MrShorty

Let us know if you have trouble with copying those formulas to the columns to the right of the distance array(s).

It depends on understanding. I think it the best way would be to upload your file Because every time I change the place of the column.

I will upload when I can (could be a day or two), and I agree it depends on understanding. I was hoping that, since you were the one to write the formulas that calculate length and distance in U:Y, that you would understand that part of the calculation well enough to copy/move/recreate the same computation in other areas of the spreadsheet. Is there something about the U:Y computation that you don't understand? Do you understand the lookup functions I used to get the SL number inputs for the U:Y computations?

My mathematics is not something good but I am trying to learn from my side.
How to make a serial from Lookup Function It is still to question for me and I try daily to get this answer in another way.

My mathematics is not something good but I am trying to learn from my side.

Your mathematics was good enough to put the U:Y formulas together previously. Is there something about that sequence of calculations that you do not understand well enough to recreate?

How to make a serial from Lookup Function It is still to question for me

In some ways, that is the bulk of the question. Did you understand the lookup (MATCH()) function that was used in column CF of L&D8? As I explained, it is not the final answer, but it is a quick and easy lookup for the closest SL number to the point given in columns G:H.

Here's my latest file. My additions are in column CH:DL. Note that these are mostly copies of he O:Y calculation with a few minor adjustments to the references. My notes in column DL explain how to choose SL number from DI:DJ -- and also note the one case where this algorithm fails. I expect it will work if expanded to test more than 2 SL numbers. Do you understand the test in CT and DG that checks if a given segment can be the correct segment?

Are we getting closer?

Thank you for giving a sheet. This is a little long sheet but works well, I have tested it with another value It is working very well.
I have created a formula, it will work to adding true value both segments as showing column DI:DJ and also it will solve the errors of all segments.
Put the formula in "DK9" and drag down

Please Login or Register  to view this content.

I am very grateful to you and Thank you so much for the help.

If you are satisfied, then I am satisfied as well. However, I note that this logic does not give the same result as our manually chosen values in column O for rows 33, 41, and 45. If I understand the logic, in some of the cases where it did match, it was luck and not because the logic of the formula made the correct choice. I had gotten the impression that our goal was to exactly match Autocad's output, and I don't think this logic is going to exactly match Autocad.

If you're satisfied, then I won't worry about it any more. I would suggest that, if the goal really was to exactly recreate Autocad's results, then more work needs to be done.

Did you read and understand the notes I left in column DL of my sheet? In those notes, I tried to explain the decision process I would have used. What did you not understand in my notes?

If I understand your questions:

if Serial 33 and 44 is correct, then we select Left Segment but there are many Segments in CT and DG It matches.

This is one of my questions at this point -- how many segments need to be tested to be sure that we always choose the correct segment. As I noted in my file, testing 2 segments was able to capture almost all of them, but left at least one incorrectly solved. I expect that testing a few more segments would fix this, but I am not sure how many segments would be needed. 3 segments? 4 segments? as many as 5 segments?

How do we know if we have to choose the left segment in which condition or to choose smaller segment?

As my notes tried to explain in the file, choose the segment that is closer to the point. In the case of row 33, the tests in columns CT and DG indicate that either segment could be correct. So we would look at the distance results for each (CR which is -6.39 and DE which is -7.11). The one closer to 0 (in this case -6.39 is closer to 0 than -7.11) is the one we want to choose.

As my notes tried to explain in the file, choose the segment that is closer to the point. In the case of row 33, the tests in columns CT and DG indicate that either segment could be correct. So we would look at the distance results for each (CR which is -6.39 and DE which is -7.11). The one closer to 0 (in this case -6.39 is closer to 0 than -7.11) is the one we want to choose.

if i choose a small number from CR and DE but still some segment did not match from rows 48,51,54,89,92,94,95,96.

Please Login or Register  to view this content.

It looks like you designed your test to work for cases where the distances are less than 0, and those cases where it fails are those cases where the distances are greater than 0.

Is there a reason you are avoiding the ABS() function in this test? I find the ABS() function is usually the easiest way to test "closest to 0" when numbers can be positive or negative. I would have used =IF(ABS(CR9)<=ABS(DE9),DI9,DJ9). Using the ABS() function like this should fix up those that did not work -- except for row 94.

Originally Posted by Sudipta2020

Please check this excel

What excel? If you are trying to upload a file, you need at least 10 posts before you can do that...plus this thread is over 3 years old