Hello! I tried posting this question in another forum, but no one has been able to figure it out. It seemed like the easiest question I've ever asked, but it's stumping people more than any complex thing I've asked about.
I need to create a formula that scans cells A2:S2 and gives a "Pass"/"Fail" condition based on whether all the cells are between an upper and lower tolerance on a tolerance tab. If each individual cell is between tolerances (lets say 90-105), then "Pass", otherwise, "Fail". Sometimes only 2 or 3 of the cells in row 2 will contain a number. Sometimes all 19 cells will have data. When a cell is not used, NA() is used to cause an error, so the formula needs to know to ignore the error cells and only count cells with a value (although I can change the NA() to a word or just a blank if needed). I've tried 20 different If statements and 30 different Countifs...none work! I can get them to read only one side of the tolerance. It seems like this would be a common problem, but so far I haven't had luck finding any info on how to solve this.
This formula is in cell A2 and then I autofilled to S2: IF(Data!D24=0,NA(),COUNTIF(Data!D6:D502,">="&Tol!$B$3)-COUNTIF(Data!D6:D502,">"&Tol!$C$3))
I have a tolerance tab setup with the lower tolerance in cell B4 and an upper tolerance (which sometimes does not exist so I type "NA" into the cell) in cell C4.
I am trying to input the Pass/Fail statement in cell V2. I have tried a ton of formulas and am currently messing with this one that someone recommended on another forum to no avail: IF(SUM(COUNTIF(A2:S2,NA()),COUNTIF(A2:S2,"<="&Tol!B4),COUNTIF(A2:S2,">="&Tol!C4))=19,"Pass","Fail")
I've attached a quick snapshot of a sample sheet I built that's similar as an example (not my actual workbook since it contains sensitive data). It shows a tolerance of 90-NA since there's no upper tolerance, but I can't get it to work even if I enter an upper tolerance.
Thanks for the help or just taking a stab at it!
I need to create a formula that scans cells A2:S2 and gives a "Pass"/"Fail" condition based on whether all the cells are between an upper and lower tolerance on a tolerance tab. If each individual cell is between tolerances (lets say 90-105), then "Pass", otherwise, "Fail". Sometimes only 2 or 3 of the cells in row 2 will contain a number. Sometimes all 19 cells will have data. When a cell is not used, NA() is used to cause an error, so the formula needs to know to ignore the error cells and only count cells with a value (although I can change the NA() to a word or just a blank if needed). I've tried 20 different If statements and 30 different Countifs...none work! I can get them to read only one side of the tolerance. It seems like this would be a common problem, but so far I haven't had luck finding any info on how to solve this.
This formula is in cell A2 and then I autofilled to S2: IF(Data!D24=0,NA(),COUNTIF(Data!D6:D502,">="&Tol!$B$3)-COUNTIF(Data!D6:D502,">"&Tol!$C$3))
I have a tolerance tab setup with the lower tolerance in cell B4 and an upper tolerance (which sometimes does not exist so I type "NA" into the cell) in cell C4.
I am trying to input the Pass/Fail statement in cell V2. I have tried a ton of formulas and am currently messing with this one that someone recommended on another forum to no avail: IF(SUM(COUNTIF(A2:S2,NA()),COUNTIF(A2:S2,"<="&Tol!B4),COUNTIF(A2:S2,">="&Tol!C4))=19,"Pass","Fail")
I've attached a quick snapshot of a sample sheet I built that's similar as an example (not my actual workbook since it contains sensitive data). It shows a tolerance of 90-NA since there's no upper tolerance, but I can't get it to work even if I enter an upper tolerance.
Thanks for the help or just taking a stab at it!
