Excel Formula question

[Hankerton]

I want to write a formula for a particular cell, where if ANY cell in a particular column = "Pass", I want this particular cell to say "Pass". If ANY cell in the same column = "Fail" I want this particular cell to say "Fail"

Sounds easy right? Can't figure this one out.

 

[JulesMaximus]

NM, I don't think this is what you're asking...

 

[dullard]

What if a cell says pass and another in the same column says fail. Then what should it display?

 

[sswingle]

Originally posted by: dullard
What if a cell says pass and another in the same column says fail. Then what should it display?

If ANY cell in the same column = "Fail" I want this particular cell to say "Fail

Learn to read

 

[dullard]

Originally posted by: ScottSwingleComputers
If ANY cell in the same column = "Fail" I want this particular cell to say "Fail

Learn to read

ScottSwingleComputers, can you read? Look here:

Originally posted by: Hankerton
where if ANY cell in a particular column = "Pass", I want this particular cell to say "Pass".

So, if a column has at least one pass and at least one fail, then one of the two conditions will be violated. Thus my question arises. Which condition takes presidence?

Try this example:
A1: "asfsadfd"
A2: "4552345"
A3: "Pass"
A4: "Fail"
A5: "$#@%@#"

(1) If any of A1-A5 says pass, then cell B1 = "pass"
(2) If any of A1-A5 says fail, then cell B1 = "fail"

Now, should B1 say pass for #1? Or should B1 say fail for #2? Or should B1 say "error I cannot satisfy both #1 and #2 at the same time"?

Once this question is answered, then we can answer the Excel formula question.

 

[Hankerton]

Originally posted by: dullard

Originally posted by: ScottSwingleComputers
If ANY cell in the same column = "Fail" I want this particular cell to say "Fail

Learn to read

ScottSwingleComputers, can you read? Look here:

Originally posted by: Hankerton
where if ANY cell in a particular column = "Pass", I want this particular cell to say "Pass".

So, if a column has at least one pass and at least one fail, then one of the two conditions will be violated. Thus my question arises. Which condition takes presidence?

Try this example:
A1: "asfsadfd"
A2: "4552345"
A3: "Pass"
A4: "Fail"
A5: "$#@%@#"

(1) If any of A1-A5 says pass, then cell B1 = "pass"
(2) If any of A1-A5 says fail, then cell B1 = "fail"

Now, should B1 say pass for #1? Or should B1 say fail for #2? Or should B1 say "error I cannot satisfy both #1 and #2 at the same time"?

I'm sorry, I should have clarified. Failure takes presedence over anything else. They would all have to pass for it to say Pass. Only one would have to Fail to say "Fail". But I don't want to include a whole column, just a section of a column (e.g. E7:E25)

 

[sswingle]

If it has one pass, and one fail, then the condition "If ANY cell says fail" has been met.

 

[sswingle]

Originally posted by: Hankerton

Originally posted by: dullard

Originally posted by: ScottSwingleComputers
If ANY cell in the same column = "Fail" I want this particular cell to say "Fail

Learn to read

ScottSwingleComputers, can you read? Look here:

Originally posted by: Hankerton
where if ANY cell in a particular column = "Pass", I want this particular cell to say "Pass".

So, if a column has at least one pass and at least one fail, then one of the two conditions will be violated. Thus my question arises. Which condition takes presidence?

Try this example:
A1: "asfsadfd"
A2: "4552345"
A3: "Pass"
A4: "Fail"
A5: "$#@%@#"

(1) If any of A1-A5 says pass, then cell B1 = "pass"
(2) If any of A1-A5 says fail, then cell B1 = "fail"

Now, should B1 say pass for #1? Or should B1 say fail for #2? Or should B1 say "error I cannot satisfy both #1 and #2 at the same time"?

I'm sorry, I should have clarified. Failure takes presedence over anything else. They would all have to pass for it to say Pass. Only one would have to Fail to say "Fail". But I don't want to include a whole column, just a section of a column (e.g. E7:E25)

No need to clariy. It made perfect sense at the get go.

 

[giantpinkbunnyhead]

Originally posted by: JulesMaximus
=if(A1="pass","pass","fail")

This will work but I'm not 100% sure that's what you want it to do. Any cell that is not equal to pass will produce the result fail.

That will work for just one cell; however he wants the whole column to be the range, so:

=if(countif(A:A,"pass")>0,"pass","fail")

will work where A:A is column A or otherwise the column of choice. If the priority needs to be switched, simply swap pass for fail in the formula.

 

[dullard]

Originally posted by: ScottSwingleComputers
If it has one pass, and one fail, then the condition "If ANY cell says fail" has been met.

But the condition "if ANY cell says pass then say PASS" has NOT been met.

Are you really this dense?

 

[Syrch]

give me an initial cell range and i can return you a fomula. Or do you wnt this for ALL cells?

 

[sswingle]

Originally posted by: dullard

Originally posted by: ScottSwingleComputers
If it has one pass, and one fail, then the condition "If ANY cell says fail" has been met.

But the condition "if ANY cell says pass" has NOT been met.

Are you really this dense?

But if you read the entire thing logically, you check if any pass. One does, the cell is marked pass. THEN if any are fail, the cell is marked fail.

Are you really this dense?

 

[theknight571]

How bout this:

=IF(ISNA(VLOOKUP("fail",E7:E25,1,FALSE)),"Pass","Fail")

This way, if Fail shows up anywhere in the column range it returns Fail.

So if they all say Pass it returns Pass, if any cell in the range says Fail, then it returns Fail no matter how many other cells say Pass.

At least that's how I'm understanding the requirement.

 

[dullard]

Originally posted by: ScottSwingleComputers
But if you read the entire thing logically, you check if any pass. One does, the cell is marked pass. THEN if any are fail, the cell is marked fail.

Are you really this dense?

You added an extra condition. You did the pass check first THEN the fail check. I was simply asking the order that he wanted because he didn't specify the order. You ASSUMED the order. If it was typed in the opposite order, would you have changed your assumption?

If this picture as a cat in it, then type "cat" in a reply to me or if that picture has a dog in it, then type "dog" in that reply to me. ONLY reply with one answer and it must be either "dog" or "cat" and nothing else.

Go.

 

[Syrch]

=IF(A1 = "pass", "pass", "fail") here you can put pass or fail in a1 and with this pasted as your formula for b1 it will return the desired results.

 

[sswingle]

Originally posted by: dullard

Originally posted by: ScottSwingleComputers
But if you read the entire thing logically, you check if any pass. One does, the cell is marked pass. THEN if any are fail, the cell is marked fail.

Are you really this dense?

You added an extra condition. You did the pass check first THEN the fail check. I was simply asking the order that he wanted because he didn't specify the order. You ASSUMED the order. If it was typed in the opposite order, would you have changed your assumption?

If this picture as a cat in it, then type "cat" in a reply to me or if that picture has a dog in it, then type "dog" in that reply to me. ONLY reply with one answer and it must be "dog" or "cat".

Go.

But in a situation with a pass fail requirements, that is the only solution that makes sense.
AND it would make sense that he would type it on the order he wanted it to be.

 

[Syrch]

now if this is for grades and you are teacher and you want to do grade ranges thats a little different. Say you want to say if student X got a 54% thats failing then I would need the pass fail range for the class and i can give you that formula.

 

[dullard]

Originally posted by: ScottSwingleComputers
But in a situation with a pass fail requirements, that is the only solution that makes sense.
AND it would make sense that he would type it on the order he wanted it to be.

I agree with you in some cases, but not in others.
[*]Supose you had 100 questions on an assignment, you passed 99, and failed one. Should you automatically fail the assignment? Probably not. The pass would probably take presidence. And Pass should override, in fact most teachers/professors would give than an A+ grade.
[*]Suppose you were checking 100 aircraft parts for safety; you passed 99 parts and failed one part. Should the airline fly that plane? Definitely not. The fail should take presidence.

Oh and one last question, Hankerton. If nothing in the column says "pass" and nothing says "fail", what should it say?

 

[sswingle]

Originally posted by: dullard

Originally posted by: ScottSwingleComputers
But in a situation with a pass fail requirements, that is the only solution that makes sense.
AND it would make sense that he would type it on the order he wanted it to be.

I agree with you in some cases, but not in others.
[*]Supose you had 100 questions on an assignment, you passed 99, and failed one. Should you automatically fail the assignment? Probably not. The pass would probably take presidence. And Pass should override, in fact most teachers/professors would give than an A+ grade.
[*]Suppose you were checking 100 aircraft parts for safety; you passed 99 parts and failed one part. Should the airline fly that plane? Definitely not. The fail should take presidence.

Oh and one last question, Hankerton. If nothing in the column says "pass" and nothing says "fail", what should it say?

I agree it wouldnt work in the first example.

And as for nothing saying either, he didnt specify that

 

[dullard]

Originally posted by: Hankerton
I'm sorry, I should have clarified. Failure takes presedence over anything else. They would all have to pass for it to say Pass. Only one would have to Fail to say "Fail". But I don't want to include a whole column, just a section of a column (e.g. E7:E25)

Oh I missed this post.

Would this do the trick?

=IF(COUNTIF(E7:E25,"pass")=COUNTA(E7:E25),"pass","fail")

This says "pass" if all non-blank cells between E7 and E25 says "pass". Otherwise it says "fail". That is, failure takes over and rules. Note: if there are undetermined tests, leaving it blank will give a "pass" result, or filling anything other that "pass" will give a "fail" result. I could adjust it for these undetermined tests to display "undetermined" if you need a change.

 

[Syrch]

yup, =IF(COUNTIF(E7:E25,"pass")=COUNTA(E7:E25),"pass","fail") works, good job dullard

 

[theknight571]

Being this is ATOT... I'm really surprised that no one yelled about him posting the question in the wrong forum.

BTW...if interested I wrote a VBA solution for this too.

 

[JS80]

Originally posted by: theknight571
How bout this:

=IF(ISNA(VLOOKUP("fail",E7:E25,1,FALSE)),"Pass","Fail")

This way, if Fail shows up anywhere in the column range it returns Fail.

So if they all say Pass it returns Pass, if any cell in the range says Fail, then it returns Fail no matter how many other cells say Pass.

At least that's how I'm understanding the requirement.

:thumbsup:

 

[dullard]

Oh, I like this version:

=IF(COUNTIF(E7:E25,"fail")>0,"Fail",IF(COUNTBLANK(E7:E25)>0,"Tests are incomplete",IF(COUNTIF(E7:E25,"pass")=COUNTA(E7:E25),"Pass","Indeterminant")))

[*]If any cell says "fail", then it says fail.
[*]If any cell is blank, it says that the tests are incomplete.
[*]If any cell is inappropriately answered, it says it is indeterminant.
[*]If all cells say "pass", then it says pass.

 

[Hankerton]

Originally posted by: dullard
Oh, I like this version:

=IF(COUNTIF(E7:E25,"fail")>0,"Fail",IF(COUNTBLANK(E7:E25)>0,"Tests are incomplete",IF(COUNTIF(E7:E25,"pass")=COUNTA(E7:E25),"Pass","Indeterminant")))

[*]If any cell says "fail", then it says fail.
[*]If any cell is blank, it says that the tests are incomplete.
[*]If any cell is inappropriately answered, it says it is indeterminant.
[*]If all cells say "pass", then it says pass.

That is a great version, does very close to what I'm trying to do. But what if I made the problem more difficult:

Lets say I have 10 tests

The results could be: Pass, Fail, In Progress, or Blocked

If ALL cells equal Pass, the test is marked "Pass"
If ANY cell Fails, the test is marked "Fail"
If ANY cell is In Progress, and NONE are marked "Fail" OR "Blocked", then the overall test is marked "In Progress"
If ANY cell is Blocked, and NONE are marked "Fail", then the overall test is marked "Blocked" (Even if there are some cells marked "In Progress")

I know this is a tough problem, but I wanted to see if this can be worked out within Excels formula limitations.

Thanks again for any help!! :beer:'s for everyone!!