Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi,
I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
1. calculate the distance betwenn the centers of the two circles.
2. if the sum of the radii of the two circles exceeds that distance, the circles overlap. -- Gary''s Student gsnu200712 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
=IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"*X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Magadevan,
You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"*X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"**X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"**X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"**X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
The slot can be oriented anywhere on the part but it has two types as
I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message ups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"**X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any co-ordinate functions in excel? I would appreciate if anyone can help me to build a good formula for this problem. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
I am also given the angle of slot orientation if that helps.
Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"***X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem.. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any ... read more »- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
I think you would need the XY coordinates of the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message ups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"***X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two circles. I have the co-ordinates and radius of the first circle and the second circle. The condition that should be checked is, if the co-ordinates of the second circle is in the range of the first circles, then it should assign a value (0, 1, or X) based on the condition. For example, the co-ordinates and radius of the first circle is (x,y, r) = (1.080, 3.520, 0.062) and for the second circle, it is (x,y, r) = (0.922, 3.4411, 0.295). I want to check if the second circle is in the range on the first circle and if it is in the range, the value should be 0. If the second circle is out of range, the value should be 1. If the second circle is blocking the first circle or very closely touching the first circle, the value should be X. The answer to the proposed problem should be 0 as I have a picture. Are there any ... read more »- Hide quoted text - - Show quoted text - |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
It can be easy to find the coordinates of the 4 corners of the
rectangle since I am given the angle in which the slot (center) is oriented, the width of the slot and the radius of the slot (semi- circle) from (0,0). Using cosine law, I can find the coordinates of the corners of the rectangle. Assuming that I am given (x1,y1)...... (x4, y4) for the rectangle how am i going to use this info to see if the sensor is in that area? Thanks Mahadevan Swamy On Apr 4, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, I think you would need the XY coordinates of the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message ups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part hole co- ordinates (in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"***X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and R coordinates for each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle one in column A, circle 2 in column B: =IF(SQRT((A1-B1)^2+(A2-B2)^2) = (A3+B3),"Touching", IF(SQRT((A1-B1)^2+(A2-B2)^2) <= ABS(A3-B3),"Completely Overlapping",IF(SQRT((A1-B1)^2+(A2-B2)^2) < (A3+B3),"Partially Overlapping", "Separated"))) There are four possible states: not touching, just touching, partially overlapping, completely overlapping. You can change the strings in the formulas to whatever values you want to return. HTH, Bernie MS Excel MVP wrote in message groups.com... Hi, I have two ... read more » |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
This is the logic I have written for the slots being oriented at different angles. The way I have numbered is starting with the top right corner of the rectangle and then top left corner of the rectangle. Conditions for getting 0 For 0 < angle < 90, x3<x<x2 y3<y<y2 x4<x<x1 y4<y<y1 For 90<angle<180 x1< x < x4 y4<y<y1 x2<x<x3 y3<y<y2 For 180<angle<270 x1<x<x4 y1<y<y4 x2<x<x3 y2<y<y3 For 270<angle<360 x4<x<x1 y1<y<y4 x2<x<x2 y2<y<y3 The two sets conditions are written for each angle if the sensor is out of range for the circle and in the range of rectangle at two places (left and right). In the data structure that you have given me, I could put up a condition where if the row for slot is equal to 0, then prevent executing the function that I have written for slot or else if the sensor is in the range of the circle and rectangle, then execute the conditions that I have given above. What is your suggestion? Thanks Mahadevan Swamy On Apr 4, 8:05 pm, wrote: It can be easy to find thecoordinatesof the 4 corners of the rectangle since I am given the angle in which the slot (center) is oriented, the width of the slot and the radius of the slot (semi- circle) from (0,0). Using cosine law, I can find thecoordinatesof the corners of the rectangle. Assuming that I am given (x1,y1)...... (x4, y4) for the rectangle how am i going to use this info to see if the sensor is in that area? Thanks Mahadevan Swamy On Apr 4, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, I think you would need the XYcoordinatesof the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message oups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you.. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part holeco- ordinates(in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"***X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and Rcoordinatesfor each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle ... read more » |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Mahadevan,
Your logic won't work for rectangles (slots) that are not orthogonal to the axes. I will write a formula that uses Excel's FORECAST function to compute the actual values of the slot's edges at the X and Y coordinates of the sensor.... I've been meaning to do that, but I haven't had time.... so just hang in there a bit... Bernie wrote in message oups.com... Hi Bernie, This is the logic I have written for the slots being oriented at different angles. The way I have numbered is starting with the top right corner of the rectangle and then top left corner of the rectangle. Conditions for getting 0 For 0 < angle < 90, x3<x<x2 y3<y<y2 x4<x<x1 y4<y<y1 For 90<angle<180 x1< x < x4 y4<y<y1 x2<x<x3 y3<y<y2 For 180<angle<270 x1<x<x4 y1<y<y4 x2<x<x3 y2<y<y3 For 270<angle<360 x4<x<x1 y1<y<y4 x2<x<x2 y2<y<y3 The two sets conditions are written for each angle if the sensor is out of range for the circle and in the range of rectangle at two places (left and right). In the data structure that you have given me, I could put up a condition where if the row for slot is equal to 0, then prevent executing the function that I have written for slot or else if the sensor is in the range of the circle and rectangle, then execute the conditions that I have given above. What is your suggestion? Thanks Mahadevan Swamy On Apr 4, 8:05 pm, wrote: It can be easy to find thecoordinatesof the 4 corners of the rectangle since I am given the angle in which the slot (center) is oriented, the width of the slot and the radius of the slot (semi- circle) from (0,0). Using cosine law, I can find thecoordinatesof the corners of the rectangle. Assuming that I am given (x1,y1)...... (x4, y4) for the rectangle how am i going to use this info to see if the sensor is in that area? Thanks Mahadevan Swamy On Apr 4, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, I think you would need the XYcoordinatesof the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message oups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part holeco- ordinates(in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"***X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thank you for your solution. I have a very complicated problem. The problem is that I have a big circle which consists of 32 small circles oriented in different places. These circles are sensors that has to read a part. When the blueprint of the part is placed on top of these sensors, these sensors should read the part. This blueprint consists of holes in the drawing and where ever there is a hole on top of the sensor, the sensor should read 0 or else a 1. That;s why we were discussing a small aspect of the problem. I have a row that consists of X, Y and Rcoordinatesfor each hole in the part and a list of sensors with their X,Y and R coordinates. I am trying to build a logic that will help me to see whether the sensor is in the range of the hole and execute decisions to produce some results. I would like to send my files to you if you want to take a look at them to understand them better. I would appreciate your help on this. Thanks Mahadevan On Mar 27, 12:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Simple algebra: The distance between two points is the square root of the sum of the squares of the differences between the X values and Y values (Pythagorean Theorom): With X values in row 1, Y values in row 2, R values in row 3, and values for circle ... read more » |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
Your condition: - SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E $4),"Completely Overlapping". In this condition, you are saying if the sensor touches the hole from the inside, then that is a 0 and in the other condition, you are saying that if the sensor is touching the egde of the hole then that would be an X (touching). For both of these conditions, it should be an X. So my formula would be OR(IF(SQRT(($B5-E $2)^2+($C5-E$3)^2) = ABS($D5-E$4),"X"), IF(SQRT(($B5-E$2)^2+($C5-E $3)^2) = $D5+E$4,"X")). Is this right? If the sensor is beyond the range of the circle, it would be SQRT((x2- x1)^2+(y2-y1)^2) ABS(r2-r1). r2 is the radius of the sensor and r1 is the radius of the hole. Do you agree? How is the formula for the slot coming along? There are some parameters that could be used for the calculation and they a angle of the slot orientation, length from (0,0) to the end of the slot, width of the slot and radius of the chamfer. For cast slot (the radius of the chamfer would be a quarter of the circle and for machined slot, the radius of chamfer would be 1/2 of the circle completely. Thanks Mahadevan Swamy On Apr 8, 8:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Your logic won't work for rectangles (slots) that are not orthogonal to the axes. I will write a formula that uses Excel's FORECAST function to compute the actual values of the slot's edges at the X and Ycoordinatesof the sensor.... I've been meaning to do that, but I haven't had time.... so just hang in there a bit... Bernie wrote in message oups.com... Hi Bernie, This is the logic I have written for the slots being oriented at different angles. The way I have numbered is starting with the top right corner of the rectangle and then top left corner of the rectangle. Conditions for getting 0 For 0 < angle < 90, x3<x<x2 y3<y<y2 x4<x<x1 y4<y<y1 For 90<angle<180 x1< x < x4 y4<y<y1 x2<x<x3 y3<y<y2 For 180<angle<270 x1<x<x4 y1<y<y4 x2<x<x3 y2<y<y3 For 270<angle<360 x4<x<x1 y1<y<y4 x2<x<x2 y2<y<y3 The two sets conditions are written for each angle if the sensor is out of range for the circle and in the range of rectangle at two places (left and right). In the data structure that you have given me, I could put up a condition where if the row for slot is equal to 0, then prevent executing the function that I have written for slot or else if the sensor is in the range of the circle and rectangle, then execute the conditions that I have given above. What is your suggestion? Thanks Mahadevan Swamy On Apr 4, 8:05 pm, wrote: It can be easy to find thecoordinatesof the 4 corners of the rectangle since I am given the angle in which the slot (center) is oriented, the width of the slot and the radius of the slot (semi- circle) from (0,0). Using cosine law, I can find thecoordinatesof the corners of the rectangle. Assuming that I am given (x1,y1)...... (x4, y4) for the rectangle how am i going to use this info to see if the sensor is in that area? Thanks Mahadevan Swamy On Apr 4, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, I think you would need the XYcoordinatesof the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message oups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part holeco- ordinates(in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, =IF(SUM(E5:AJ5) = 32,1, IF(ISNUMBER(MATCH(0,E5:AJ5,FALSE)),0,IF(ISNUMBER(M ATCH("X",E5:AJ5,FALSE)),"****X","Other"))) Should work: - change the E5:AJ5 to the range address, and change the 32 to the maximum expected number of 1's. HTH, Bernie MS Excel MVP wrote in message roups.com... Hi Bernie, Thanks for your unique solution. It helped me a lot. I would like to know how I can summarize the table of values that I got. For example, I got a row of 1's, so I want the result to be a 1 for the particular sensor. If there is 0 or X anywhere in the row, I want the result to be 0 or X. There can be only one value for the sensor. Is there a function that will do this job? Thanks for your help Mahadevan Swamy On Mar 28, 9:11 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, You could make a table, with the 'sensor' circle data starting in row 5, in columns B (X coordinate), C (Y coord), and D (R values). Put the 'hole' circle data, starting in Column E in rows 2 (X coord), 3 (Y coord), and 4 (R value). (Row 1 and column A could be used for data). Then in cell E5: =IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) = ($D5+E$4),"Touching", IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E$4),"Completely Overlapping",IF(SQRT(($B5-E$2)^2+($C5-E$3)^2) < ($D5+E$4),"Partially Overlapping", "Separated"))) Copy this to match both your sensor and hole data, from E5, to say, E5:AA40, and you will get a table of values - again, change the strings to whatever numeric values you want to have returned. HTH, Bernie MS Excel MVP ... read more »- Hide quoted text - - Show quoted text - |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
These are the conditions that I have for the sensor to read the holes. For the result of 0, I have SQRT((x2-x1)^2 + (y2-y1)^2) < abs(r2-r1) ---------------- (uncovered) For result of X, I have SQRT((x2-x1)^2 + (y2-y1)^2) = abs(r2-r1) --------------------- (touching from the inside) AND SQRT((x2-x1)^2 + (y2-y1)^2) <= r1+r2 ----------------(Touching from outside and partially blocked) However, I have a small problem. When the edge of the hole is very close to the sensor by 30 thou, it should be regarded as X (in other words, it should not exceed 0.03 or else it would be a 1 or a 0 depending on whether the sensor in inside the hole or outside of it). Can you suggest a solution for that? Thanks Mahadevan On Apr 11, 2:35 pm, wrote: Hi Bernie, Your condition: - SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E $4),"Completely Overlapping". In this condition, you are saying if the sensor touches the hole from the inside, then that is a 0 and in the other condition, you are saying that if the sensor is touching the egde of the hole then that would be an X (touching). For both of these conditions, it should be an X. So my formula would be OR(IF(SQRT(($B5-E $2)^2+($C5-E$3)^2) = ABS($D5-E$4),"X"), IF(SQRT(($B5-E$2)^2+($C5-E $3)^2) = $D5+E$4,"X")). Is this right? If the sensor is beyond the range of the circle, it would be SQRT((x2- x1)^2+(y2-y1)^2) ABS(r2-r1). r2 is the radius of the sensor and r1 is the radius of the hole. Do you agree? How is the formula for the slot coming along? There are some parameters that could be used for the calculation and they a angle of the slot orientation, length from (0,0) to the end of the slot, width of the slot and radius of the chamfer. For cast slot (the radius of the chamfer would be a quarter of the circle and for machined slot, the radius of chamfer would be 1/2 of the circle completely. Thanks Mahadevan Swamy On Apr 8, 8:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Your logic won't work for rectangles (slots) that are not orthogonal to the axes. I will write a formula that uses Excel's FORECAST function to compute the actual values of the slot's edges at the X and Ycoordinatesof the sensor.... I've been meaning to do that, but I haven't had time.... so just hang in there a bit... Bernie wrote in message roups.com... Hi Bernie, This is the logic I have written for the slots being oriented at different angles. The way I have numbered is starting with the top right corner of the rectangle and then top left corner of the rectangle. Conditions for getting 0 For 0 < angle < 90, x3<x<x2 y3<y<y2 x4<x<x1 y4<y<y1 For 90<angle<180 x1< x < x4 y4<y<y1 x2<x<x3 y3<y<y2 For 180<angle<270 x1<x<x4 y1<y<y4 x2<x<x3 y2<y<y3 For 270<angle<360 x4<x<x1 y1<y<y4 x2<x<x2 y2<y<y3 The two sets conditions are written for each angle if the sensor is out of range for the circle and in the range of rectangle at two places (left and right). In the data structure that you have given me, I could put up a condition where if the row for slot is equal to 0, then prevent executing the function that I have written for slot or else if the sensor is in the range of the circle and rectangle, then execute the conditions that I have given above. What is your suggestion? Thanks Mahadevan Swamy On Apr 4, 8:05 pm, wrote: It can be easy to find thecoordinatesof the 4 corners of the rectangle since I am given the angle in which the slot (center) is oriented, the width of the slot and the radius of the slot (semi- circle) from (0,0). Using cosine law, I can find thecoordinatesof the corners of the rectangle. Assuming that I am given (x1,y1)...... (x4, y4) for the rectangle how am i going to use this info to see if the sensor is in that area? Thanks Mahadevan Swamy On Apr 4, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, I think you would need the XYcoordinatesof the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message oups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Co-ordinates
Hi Bernie,
Any luck with the slots? Thanks Mahadevan Swamy On Apr 14, 12:04 am, wrote: Hi Bernie, These are the conditions that I have for the sensor to read the holes. For the result of 0, I have SQRT((x2-x1)^2 + (y2-y1)^2) < abs(r2-r1) ---------------- (uncovered) For result of X, I have SQRT((x2-x1)^2 + (y2-y1)^2) = abs(r2-r1) --------------------- (touching from the inside) AND SQRT((x2-x1)^2 + (y2-y1)^2) <= r1+r2 ----------------(Touching from outside and partially blocked) However, I have a small problem. When the edge of the hole is very close to the sensor by 30 thou, it should be regarded as X (in other words, it should not exceed 0.03 or else it would be a 1 or a 0 depending on whether the sensor in inside the hole or outside of it). Can you suggest a solution for that? Thanks Mahadevan On Apr 11, 2:35 pm, wrote: Hi Bernie, Your condition: - SQRT(($B5-E$2)^2+($C5-E$3)^2) <= ABS($D5-E $4),"Completely Overlapping". In this condition, you are saying if the sensor touches the hole from the inside, then that is a 0 and in the other condition, you are saying that if the sensor is touching the egde of the hole then that would be an X (touching). For both of these conditions, it should be an X. So my formula would be OR(IF(SQRT(($B5-E $2)^2+($C5-E$3)^2) = ABS($D5-E$4),"X"), IF(SQRT(($B5-E$2)^2+($C5-E $3)^2) = $D5+E$4,"X")). Is this right? If the sensor is beyond the range of the circle, it would be SQRT((x2- x1)^2+(y2-y1)^2) ABS(r2-r1). r2 is the radius of the sensor and r1 is the radius of the hole. Do you agree? How is the formula for the slot coming along? There are some parameters that could be used for the calculation and they a angle of the slot orientation, length from (0,0) to the end of the slot, width of the slot and radius of the chamfer. For cast slot (the radius of the chamfer would be a quarter of the circle and for machined slot, the radius of chamfer would be 1/2 of the circle completely. Thanks Mahadevan Swamy On Apr 8, 8:32 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Your logic won't work for rectangles (slots) that are not orthogonal to the axes. I will write a formula that uses Excel's FORECAST function to compute the actual values of the slot's edges at the X and Ycoordinatesof the sensor.... I've been meaning to do that, but I haven't had time.... so just hang in there a bit... Bernie wrote in message roups.com... Hi Bernie, This is the logic I have written for the slots being oriented at different angles. The way I have numbered is starting with the top right corner of the rectangle and then top left corner of the rectangle. Conditions for getting 0 For 0 < angle < 90, x3<x<x2 y3<y<y2 x4<x<x1 y4<y<y1 For 90<angle<180 x1< x < x4 y4<y<y1 x2<x<x3 y3<y<y2 For 180<angle<270 x1<x<x4 y1<y<y4 x2<x<x3 y2<y<y3 For 270<angle<360 x4<x<x1 y1<y<y4 x2<x<x2 y2<y<y3 The two sets conditions are written for each angle if the sensor is out of range for the circle and in the range of rectangle at two places (left and right). In the data structure that you have given me, I could put up a condition where if the row for slot is equal to 0, then prevent executing the function that I have written for slot or else if the sensor is in the range of the circle and rectangle, then execute the conditions that I have given above. What is your suggestion? Thanks Mahadevan Swamy On Apr 4, 8:05 pm, wrote: It can be easy to find thecoordinatesof the 4 corners of the rectangle since I am given the angle in which the slot (center) is oriented, the width of the slot and the radius of the slot (semi- circle) from (0,0). Using cosine law, I can find thecoordinatesof the corners of the rectangle. Assuming that I am given (x1,y1)...... (x4, y4) for the rectangle how am i going to use this info to see if the sensor is in that area? Thanks Mahadevan Swamy On Apr 4, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, I think you would need the XYcoordinatesof the 4 courners of the slot. Do you have that? Or perhaps we could derive it.... Bernie wrote in message oups.com... I am also given the angle of slot orientation if that helps. Mahadevan On Mar 31, 2:28 pm, wrote: The slot can be oriented anywhere on the part but it has two types as I have described in my previous post. I am given the radius relative to the origin for the first type of slot. This radius is the distance from the orgin to the center of semi-circle. I have another information which may or may not be useful. The mounting holes (small holes outside bigger inner hole) has a center line radius measured with respect to the origin and the slot cannot extend beyond this radius (for the rectangle part). Small part of the semi-circle is extended beyond this radius but I am not given the dimension of how much it has been extended. This applies to both types of slot. I could scan this picture and send it to you if you want. Thanks for your assistance. Mahadevan Swamy On Mar 30, 4:58 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Mahadevan, Does the slot have a specific orientation, relative to the origin? HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, I haven't told you about two design features (holes) of this part. All this time, we were discussing about parts having some small holes in different locations and sensors reading them. The design feature I haven't told you is that there is a bigger inner hole (pilot bore hole) in the part and all the small holes (mounting holes, sensor holes etc..) we were discussing about, are outside of this big hole. This big hole has the coordinate (0,0,r) always. I have managed to use your formula for that kind of design and sensors will know whether they are inside the big hole or outside of it. The next design is a bit tricky. Some parts consists of this hole. This hole is called a slot which is connected to the bigger inner hole (pilot bore hole). There are two kinds of slot. One kind of slot looks like a semi-circle and a rectangle together that is connected to the pilot bore hole.If you want a picture of this, I can send it to you. I am given X, Y, and R coordinate of the semi-circle. I am also given the absolute reference of this point. The other kind of slot looks like a wide rectangle but is chamfered in the two corners. For this, I am given width of the rectangle, radius of the chamfer and distance from the end of the slot to the orgin. I could consider these slots as a circle but some area of the hole is omitted which could result giving wrong values for the sensor, if they are in that area. Is there a better formula to read these designs? We will continue our discussion of creating a macro for this later as I am trying to work on this tricky problem. Thanks Mahadevan Swamy On Mar 29, 5:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Magadevan, You could write a macro to take the values from a data table, paste them into your worksheet with the formula table, do a calculation, then copy the results into another table. That way, you would not have a HUGE workbook, and the processing would be relatively quick. Let me know if you want to do the macro. If you do, I would need to know your data structure, and how you would want the resulting table organized. I would suggest three sheets: one for the data table, one for the formula table, and one for results, though all three could go onto one sheet, in different areas. HTH, Bernie MS Excel MVP wrote in message oups.com... Hi Bernie, Thanks a ton. I would appreciate your advise on how I can organize my data structure as I have to do the same thing for 1000 part patterns. I was hoping to create a function that can scan the part holeco- ordinates(in one row) and then in another worksheet, report what each sensor got for the corresponding part number. Is there an easier way to summarize the sensor findings by performing all mathematical calculations in one formula? Thanks for your help Mahadevan Swamy On Mar 28, 6:00 pm, ... read more » |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Does Excel understand Latitude and Longitude (GPS) co-ordinates? | Excel Discussion (Misc queries) | |||
Crime Analyst needs help with co-ordinates | Excel Discussion (Misc queries) | |||
read co-ordinates | Charts and Charting in Excel | |||
Formating Latitude and Longitude co-ordinates | Excel Discussion (Misc queries) |