If All Cells Between Two Tolerances Pass...

innoin

New Member
Joined
May 4, 2017
Messages
33
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!
 

Attachments

  • sample.PNG
    sample.PNG
    8.1 KB · Views: 9

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
Try this:
mrexcel_20200416.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1Time:Fail
2#N/A#N/A3184125#N/A101393939#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A#N/A
390-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA90-NA
4Tol:90105
Sheet20
Cell Formulas
RangeFormula
V1V1=IF(OR(AGGREGATE(15,6,$A$2:$S$2,1)<$V$4,AGGREGATE(14,6,$A$2:$S$2,1)>$W$4),"Fail","Pass")
K2:S2,F2,A2:B2A2=NA()
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,067
Office Version
  1. 2019
Platform
  1. Windows
You're welcome...glad to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,693
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top