If Multiple Cells Are Between Two Values Then...

[Shemhamph]

Hello again everyone! I'm trying to create a formula that will scan a row of cells (A2:S2) and give a "Pass"/"Fail" condition if each cell is between two numbers on another tab. It also needs to ignore error (#N/A) cells and only count the cells with numbers in them.

So basically something like this...only working instead of doing nothing:

IF(AND(A2:S2<=Tol!B4,A2:S2>=Tol!C4),"Pass","Fail"))

[biex]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Someone with better solution will come.

[Shemhamph]

Hmm, the second one gives a "Pass" no matter what. The first formula only works if a single cell is out of tolerance. If multiple cells are failing, it states "Pass".

Edit: Actually, the first formula works if all the cells are above the maximum tolerance, but it states "Pass" if some cells are below the minimum and some are above the maximum.

[biex]

it actually hard for me or someone else to work on something without actually see any sample file... maybe you could provide us with some sample file

[Pepe Le Mokko]

See the yellow banner please

[Shemhamph]

Okay, I've attached a sample of similar data. Thanks for the help!

[Shemhamph]

I just realized I messed up the formula in that sample...it's supposed to be looking at row 2 not 3. Also, it needs to work if there's an upper tolerance as well (currently it shows 90-NA because there isn't one on that particular sample, but on others there are).

[JeteMc]

Try the following in cell V1: =IF(SUM(COUNTIF(A2:S2,NA()),COUNTIFS(A2:S2,">="&Tol!B4,A2:S2,"<="&Tol!C4))=19,"Pass","Fail")
If this doesn't work then it may help us if you'll provide more examples of when the cell should read Pass and of when it should read Fail. Include examples where there is and isn't an upper and/or lower (if applicable) tolerance.
Let us know if you have any questions.