Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tolerances Logic for Excel
Can anyone help in this re-post?
I have tried and tried to solve the logic of Pass/Fail to applicable tolerances: The table of tolerances are Situation A Situation B <= 60 = +20 -20 +30 -20 80 = +20 -20 +30 -30 100 = +30 -30 +40 -40 =120 = +40 -40 +40 -40 I need to measure the number of results that are out of tolerance in a list (up to 1500 records). I envisage dedicating a column to fill with "0"/"1" or "TRUE"/"FALSE" or "PASS"/FAIL". I have spent many hours on this problem and am no nearer to solving the logic than when I started. Has anyone encountered a similar situation? Or can someone "see" the logic? I would be even more impressed if someone can help with how to "highlight" in Conditional formatting so the Pass/Fail can be detected at the time of data entry. This is less important than counting the "failures". Situation A is sampling and testing the product at the point of sale where confidence the sample is representative of the batch is higher than Situation B where sampling is prior to release of the product from the production facility. The range of values are stipulated at 60, 80, 100 and 120 but it is permissible to supply values below 60 and above 120 but the tolerances remain at the lower and upper levels regardless. It is easy for a persons mind to determine pass/fail but not so easy to define for a program. The real need I have is to determine the number of non-conformances as there is a permitted nuimber of "failures" per group of 10 results. Thanking any of you wonderful experts in grateful anticipation. sincerely Jeff Smith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tolerances Logic for Excel
Jeff
I'm not sure if it's just me (probably) but I don't understand the rules of engagement here. It would help me to understand what you are trying to do and perhaps provide a formula or code if I had some specific examples of what is within and without the tolerance. If you have a value of say 85 is that out of tolerance compared to 60 +/-20 or within tolerance compared to 80 +/-20 "It is easy for a persons mind to determine pass/fail " ... maybe not so ! Maybe you could post some sample data indicating how you determine pass/fail, etc Regards Trevor "Jeff Smith" wrote in message ... Can anyone help in this re-post? I have tried and tried to solve the logic of Pass/Fail to applicable tolerances: The table of tolerances are Situation A Situation B <= 60 = +20 -20 +30 -20 80 = +20 -20 +30 -30 100 = +30 -30 +40 -40 =120 = +40 -40 +40 -40 I need to measure the number of results that are out of tolerance in a list (up to 1500 records). I envisage dedicating a column to fill with "0"/"1" or "TRUE"/"FALSE" or "PASS"/FAIL". I have spent many hours on this problem and am no nearer to solving the logic than when I started. Has anyone encountered a similar situation? Or can someone "see" the logic? I would be even more impressed if someone can help with how to "highlight" in Conditional formatting so the Pass/Fail can be detected at the time of data entry. This is less important than counting the "failures". Situation A is sampling and testing the product at the point of sale where confidence the sample is representative of the batch is higher than Situation B where sampling is prior to release of the product from the production facility. The range of values are stipulated at 60, 80, 100 and 120 but it is permissible to supply values below 60 and above 120 but the tolerances remain at the lower and upper levels regardless. It is easy for a persons mind to determine pass/fail but not so easy to define for a program. The real need I have is to determine the number of non-conformances as there is a permitted nuimber of "failures" per group of 10 results. Thanking any of you wonderful experts in grateful anticipation. sincerely Jeff Smith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tolerances Logic for Excel
I have added some examples below (in the body of the query) for
clarification. Hope it helps. Jeff. "Trevor Shuttleworth" wrote in message ... Jeff I'm not sure if it's just me (probably) but I don't understand the rules of engagement here. It would help me to understand what you are trying to do and perhaps provide a formula or code if I had some specific examples of what is within and without the tolerance. If you have a value of say 85 is that out of tolerance compared to 60 +/-20 or within tolerance compared to 80 +/-20 "It is easy for a persons mind to determine pass/fail " ... maybe not so ! Maybe you could post some sample data indicating how you determine pass/fail, etc Regards Trevor "Jeff Smith" wrote in message ... Can anyone help in this re-post? I have tried and tried to solve the logic of Pass/Fail to applicable tolerances: The table of tolerances are Situation A Situation B <= 60 = +20 -20 +30 -20 80 = +20 -20 +30 -30 100 = +30 -30 +40 -40 =120 = +40 -40 +40 -40 Example Specified =100mm. Test Result Situation A = 140mm = Fail. If situation B = Pass Specified = 100mm. Test Result Situation A = 70mm = Pass If situation B = 70 = Pass. Specified = 80mm. Test Result Situation A = 110 = Fail but Situation B = Pass. (These are all termed consistence tests, and results are physical measurements in mm) I need to measure the number of results that are out of tolerance in a list (up to 1500 records). I envisage dedicating a column to fill with "0"/"1" or "TRUE"/"FALSE" or "PASS"/FAIL". I have spent many hours on this problem and am no nearer to solving the logic than when I started. Has anyone encountered a similar situation? Or can someone "see" the logic? I would be even more impressed if someone can help with how to "highlight" in Conditional formatting so the Pass/Fail can be detected at the time of data entry. This is less important than counting the "failures". Situation A is sampling and testing the product at the point of sale where confidence the sample is representative of the batch is higher than Situation B where sampling is prior to release of the product from the production facility. The range of values are stipulated at 60, 80, 100 and 120 but it is permissible to supply values below 60 and above 120 but the tolerances remain at the lower and upper levels regardless. It is easy for a persons mind to determine pass/fail but not so easy to define for a program. The real need I have is to determine the number of non-conformances as there is a permitted nuimber of "failures" per group of 10 results. Thanking any of you wonderful experts in grateful anticipation. sincerely Jeff Smith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tolerances Logic for Excel
Jeff
OK, since the solutions aren't rolling in ... let's give it a go. I don't know if this will cover all the scenarios but it copes with your examples. First, assume you have a Tolerance Table on a separate sheet, aptly named "Tolerance Table" (no quotes). The table will look something like: Tolerance A- A+ B- B+ 60 20 20 20 30 80 20 20 30 30 100 30 30 40 40 120 40 40 40 40 The headings aren't particularly important ... they're not actually used, they just need to mean something to you. Then, on your "input" sheet, assume that the result is in column A, the specification is in column B, and the results for Situation A and Situation B are in columns C and D respectively. Result Specification Situation A Situation B 140 100 fail pass 70 100 pass pass 110 80 fail pass If row 1 is the headings as above, in cell C2 put the following formula: =IF(OR(A4<B4-VLOOKUP(B4,'Tolerance Table'!$A:$E,2,FALSE),A4B4+VLOOKUP(B4,'Tolerance Table'!$A:$E,3,FALSE)), "fail","pass") In cell D2 put the following formula: =IF(OR(A2<B2-VLOOKUP(B2,'Tolerance Table'!$A:$E,4,FALSE),A2B2+VLOOKUP(B2,'Tolerance Table'!$A:$E,5,FALSE)), "fail","pass") Drag the formulae down as far as you need to. May not be perfect but you can add tolerances to the table so you may be able to refine it if it doesn't do everything you need. Be aware that it doesn't test for "invalid" specifications so, for example, if you put 95 in the Specification (on input) you'd get #N/A in the Situation columns. You can check for this if you need to ... but you'd need to decide on the rules if/when this happens. Some test values ... Result Specification Situation A Situation B 140 100 fail pass 70 100 pass pass 110 80 fail pass 35 60 fail fail 40 60 pass pass 45 60 pass pass 50 60 pass pass 55 60 pass pass 60 60 pass pass 65 60 pass pass 70 60 pass pass 75 60 pass pass 80 60 pass pass 85 60 fail pass 90 60 fail pass 95 60 fail fail 45 80 fail fail 50 80 fail pass 55 80 fail pass 60 80 pass pass 65 80 pass pass 70 80 pass pass 75 80 pass pass 80 80 pass pass 85 80 pass pass 90 80 pass pass 95 80 pass pass 100 80 pass pass 105 80 fail pass 110 80 fail pass 115 80 fail fail What I'm still not absolutely sure of is how you want it to work at extremes .... that is way below 60 and way above 120. But it's a start. I hope this helps. Post back if you need any more help. Sorry it's taken a while to follow up but I've been away for a couple of days. Regards Trevor "Jeff Smith" wrote in message ... I have added some examples below (in the body of the query) for clarification. Hope it helps. Jeff. "Trevor Shuttleworth" wrote in message ... Jeff I'm not sure if it's just me (probably) but I don't understand the rules of engagement here. It would help me to understand what you are trying to do and perhaps provide a formula or code if I had some specific examples of what is within and without the tolerance. If you have a value of say 85 is that out of tolerance compared to 60 +/-20 or within tolerance compared to 80 +/-20 "It is easy for a persons mind to determine pass/fail " ... maybe not so ! Maybe you could post some sample data indicating how you determine pass/fail, etc Regards Trevor "Jeff Smith" wrote in message ... Can anyone help in this re-post? I have tried and tried to solve the logic of Pass/Fail to applicable tolerances: The table of tolerances are Situation A Situation B <= 60 = +20 -20 +30 -20 80 = +20 -20 +30 -30 100 = +30 -30 +40 -40 =120 = +40 -40 +40 -40 Example Specified =100mm. Test Result Situation A = 140mm = Fail. If situation B = Pass Specified = 100mm. Test Result Situation A = 70mm = Pass If situation B = 70 = Pass. Specified = 80mm. Test Result Situation A = 110 = Fail but Situation B = Pass. (These are all termed consistence tests, and results are physical measurements in mm) I need to measure the number of results that are out of tolerance in a list (up to 1500 records). I envisage dedicating a column to fill with "0"/"1" or "TRUE"/"FALSE" or "PASS"/FAIL". I have spent many hours on this problem and am no nearer to solving the logic than when I started. Has anyone encountered a similar situation? Or can someone "see" the logic? I would be even more impressed if someone can help with how to "highlight" in Conditional formatting so the Pass/Fail can be detected at the time of data entry. This is less important than counting the "failures". Situation A is sampling and testing the product at the point of sale where confidence the sample is representative of the batch is higher than Situation B where sampling is prior to release of the product from the production facility. The range of values are stipulated at 60, 80, 100 and 120 but it is permissible to supply values below 60 and above 120 but the tolerances remain at the lower and upper levels regardless. It is easy for a persons mind to determine pass/fail but not so easy to define for a program. The real need I have is to determine the number of non-conformances as there is a permitted nuimber of "failures" per group of 10 results. Thanking any of you wonderful experts in grateful anticipation. sincerely Jeff Smith |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tolerances Logic for Excel
Trevor,
Thank you for this response. Your input is very much appreciated. I shall work through this. sincerely Jeff Smith "Trevor Shuttleworth" wrote in message ... Jeff OK, since the solutions aren't rolling in ... let's give it a go. I don't know if this will cover all the scenarios but it copes with your examples. First, assume you have a Tolerance Table on a separate sheet, aptly named "Tolerance Table" (no quotes). The table will look something like: Tolerance A- A+ B- B+ 60 20 20 20 30 80 20 20 30 30 100 30 30 40 40 120 40 40 40 40 The headings aren't particularly important ... they're not actually used, they just need to mean something to you. Then, on your "input" sheet, assume that the result is in column A, the specification is in column B, and the results for Situation A and Situation B are in columns C and D respectively. Result Specification Situation A Situation B 140 100 fail pass 70 100 pass pass 110 80 fail pass If row 1 is the headings as above, in cell C2 put the following formula: =IF(OR(A4<B4-VLOOKUP(B4,'Tolerance Table'!$A:$E,2,FALSE),A4B4+VLOOKUP(B4,'Tolerance Table'!$A:$E,3,FALSE)), "fail","pass") In cell D2 put the following formula: =IF(OR(A2<B2-VLOOKUP(B2,'Tolerance Table'!$A:$E,4,FALSE),A2B2+VLOOKUP(B2,'Tolerance Table'!$A:$E,5,FALSE)), "fail","pass") Drag the formulae down as far as you need to. May not be perfect but you can add tolerances to the table so you may be able to refine it if it doesn't do everything you need. Be aware that it doesn't test for "invalid" specifications so, for example, if you put 95 in the Specification (on input) you'd get #N/A in the Situation columns. You can check for this if you need to ... but you'd need to decide on the rules if/when this happens. Some test values ... Result Specification Situation A Situation B 140 100 fail pass 70 100 pass pass 110 80 fail pass 35 60 fail fail 40 60 pass pass 45 60 pass pass 50 60 pass pass 55 60 pass pass 60 60 pass pass 65 60 pass pass 70 60 pass pass 75 60 pass pass 80 60 pass pass 85 60 fail pass 90 60 fail pass 95 60 fail fail 45 80 fail fail 50 80 fail pass 55 80 fail pass 60 80 pass pass 65 80 pass pass 70 80 pass pass 75 80 pass pass 80 80 pass pass 85 80 pass pass 90 80 pass pass 95 80 pass pass 100 80 pass pass 105 80 fail pass 110 80 fail pass 115 80 fail fail What I'm still not absolutely sure of is how you want it to work at extremes ... that is way below 60 and way above 120. But it's a start. I hope this helps. Post back if you need any more help. Sorry it's taken a while to follow up but I've been away for a couple of days. Regards Trevor "Jeff Smith" wrote in message ... I have added some examples below (in the body of the query) for clarification. Hope it helps. Jeff. "Trevor Shuttleworth" wrote in message ... Jeff I'm not sure if it's just me (probably) but I don't understand the rules of engagement here. It would help me to understand what you are trying to do and perhaps provide a formula or code if I had some specific examples of what is within and without the tolerance. If you have a value of say 85 is that out of tolerance compared to 60 +/-20 or within tolerance compared to 80 +/-20 "It is easy for a persons mind to determine pass/fail " ... maybe not so ! Maybe you could post some sample data indicating how you determine pass/fail, etc Regards Trevor "Jeff Smith" wrote in message ... Can anyone help in this re-post? I have tried and tried to solve the logic of Pass/Fail to applicable tolerances: The table of tolerances are Situation A Situation B <= 60 = +20 -20 +30 -20 80 = +20 -20 +30 -30 100 = +30 -30 +40 -40 =120 = +40 -40 +40 -40 Example Specified =100mm. Test Result Situation A = 140mm = Fail. If situation B = Pass Specified = 100mm. Test Result Situation A = 70mm = Pass If situation B = 70 = Pass. Specified = 80mm. Test Result Situation A = 110 = Fail but Situation B = Pass. (These are all termed consistence tests, and results are physical measurements in mm) I need to measure the number of results that are out of tolerance in a list (up to 1500 records). I envisage dedicating a column to fill with "0"/"1" or "TRUE"/"FALSE" or "PASS"/FAIL". I have spent many hours on this problem and am no nearer to solving the logic than when I started. Has anyone encountered a similar situation? Or can someone "see" the logic? I would be even more impressed if someone can help with how to "highlight" in Conditional formatting so the Pass/Fail can be detected at the time of data entry. This is less important than counting the "failures". Situation A is sampling and testing the product at the point of sale where confidence the sample is representative of the batch is higher than Situation B where sampling is prior to release of the product from the production facility. The range of values are stipulated at 60, 80, 100 and 120 but it is permissible to supply values below 60 and above 120 but the tolerances remain at the lower and upper levels regardless. It is easy for a persons mind to determine pass/fail but not so easy to define for a program. The real need I have is to determine the number of non-conformances as there is a permitted nuimber of "failures" per group of 10 results. Thanking any of you wonderful experts in grateful anticipation. sincerely Jeff Smith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tolerances in a temperature chart - conditional formatting - help! | Excel Discussion (Misc queries) | |||
engineering tolerances | Excel Discussion (Misc queries) | |||
Excel logic | Excel Discussion (Misc queries) | |||
How do I get EXCEL to experss tolerances? | Excel Discussion (Misc queries) | |||
Tolerances Problem | Excel Programming |