logical function problem

Originally Posted by meddington

Question in two parts. I currently have a spreadsheet calculating pass/fail based on a defined tolerance...for example:

value Pass/Fail Tolerances (dEab)

0.23 Pass 2
0.18 Pass 1
0.30 Pass 2

I'd like to reformat this to one row, like this:

value Pass/Fail Tolerances (dEab)
0.23, 0.18, 0.30 Pass 2, 1, 2

The problem I'm having is the pass/fail cell. Instead of a logical funtion based on two cell values (=IF(A1<=C1,"Pass","Fail") it would be have to be based on the different values in cell A1 and C1. I couldn't wrap my head around this, so I decided to keep the original format, and simply create a summary report formated in one row where the value column would look like this:

=TEXT(C24,"0.00")&", "&TEXT(C25,"0.00")&", "&TEXT(C26,"0.00")

And the pass/fail column would look like this...sort of a multiple logical function:

=IF(C24<=E24,"Pass","Fail")=IF(C25<=E25,"Pass","Fail")=IF(C26<=E26,"Pass","Fail")

my logical function reports "False" and I know I'm totally missing something here. Any advice?

Next issue is I'll have a long column of pass/fail values. I'd like to do a summary here displaying "Pass" if all values of the column are reporting "Pass" and a "Fail" if any cell reports a "Fail". This is probably the easier of the two issues I'm having, but if anyone can help it would be appreciated.

thanks

please attach a sample file in zip format.

Originally Posted by meddington

Here's the attachment regarding the second part of my question... generating a summary of a column of pass/fail results.

try this

=IF(COUNTA(D7:D18)=COUNTIF(D7:D18,"Pass"),"Pass","Fail")

Originally Posted by meddington

Perfect. Thanks!

you're welcome.

Originally Posted by meddington

Ok, got another one.

I'm wondering how to alter the code to include a third possiblity..."pass", "fail" or "Data missing". An array formula?

Hi
Please make it more clear that when you want to return each of three possibilties.
1) return "Fail" if even a single fail is found
2) return "Pass" if all are pass
3) return "Data missing" when???

Originally Posted by meddington

"Data Missing" returned when an empty cell occurs...so its more than a question about the "=IF(COUNTIF(D7:D18,"fail"),"fail","pass")" formula being able to determine a third result, and also about the logical function determining a pass/fail to determine a third result as well. A logical test simply determines a true or false outcome, so I'm wondering if there's a way to determine a third result...rather than IF(logical_test,[value_if_true],[value_if_false]), I'm wondering if a formula can look at a numeric value and if it is above a set tolerance, report "fail", if it is below a certain tolerance, report "pass", and if the value if missing (a blank cell), result in a "data missing" response.

I suppose this isn't entirely necessary, as I can have conditional formatting detemine three different formats based on the value of one cell, so I can make a cell with missing data shaded red or something, but I was just wondering if I could actually get a different value for the cell. I hope that makes sense.

does this help

=IF(COUNTBLANK(D7:D18),"Data Missing",IF(COUNTIF(D7:D18,"fail"),"fail","pass"))

Originally Posted by meddington

Yes, that's it exactly. Thanks again!

you're welcome. good to see that it helped you.