Excel Formula question

[JulesMaximus]

Originally posted by: 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.

I'd be interested. I use Excel at work quite a bit and while I do consider myself quite strong in Excel I'm still a relative noob at VBA and writing macros. I've used the record feature and edit them but that's about it.

BTW-I liked your solution. I copied it for future use as I can see a need for that in a different application. :thumbsup:

 

[dullard]

Originally posted by: Hankerton
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")

Try something along these lines. I added one extra output, just because I know it is a common typo (leaving a cell blank). But I think it otherwise does what you want it to do.

=IF(COUNTIF(E7:E25,"fail")>0,"Fail",IF(COUNTIF(E7:E25,"blocked")>0,"Blocked",IF(COUNTBLANK(E7:E25)>0,"Cell Empty, In Progress",IF(COUNTIF(E7:E25,"pass")=COUNTA(E7:E25),"Pass","In Progress"))))

Hope it helps.

 

[theknight571]

Originally posted by: JulesMaximus

Originally posted by: 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.

I'd be interested. I use Excel at work quite a bit and while I do consider myself quite strong in Excel I'm still a relative noob at VBA and writing macros. I've used the record feature and edit them but that's about it.

BTW-I liked your solution. I copied it for future use as I can see a need for that in a different application. :thumbsup:

For JulesMaximus...since he asked

I modified my function to match the revised requirements.

It doesn't check for odd entries, but could be modified to do so.

There might also be a more "cleaver" way of doing it... but this is my quick and dirty "CheckPassFail Function v0.5"

I don't like all the if..then's at the end, but like I said... it's quick and dirty.

Unfortunately, there's no "Add Code" button on this forum, so I will substitute periods in order to maintain my spacing.


Function CheckPassFail(TheRange As Range) As String
... PassCount = 0
... FailCount = 0
... MiscCount = 0
... BlockedCount = 0
... IPCount = 0
... For Each C In TheRange
...... Select Case C.Value
......... Case "pass"
............ PassCount = PassCount + 1
......... Case "fail"
............ FailCount = FailCount + 1
......... Case "blocked"
............ BlockedCount = BlockedCount + 1
......... Case "in progress"
............ IPCount = IPCount + 1
......... Case Else
............ MiscCount = MiscCount + 1
...... End Select
... Next
... If FailCount >= 1 Then
...... CheckPassFail = "Fail"
... Else
...... If MiscCount + BlockedCount + IPCount = 0 Then
......... CheckPassFail = "Pass"
...... Else
......... If (IPCount >= 1) And (BlockedCount = 0) Then
............ CheckPassFail = "In Progress"
......... Else
............ If (BlockedCount >= 1) Then
............... CheckPassFail = "Blocked"
............ Else
............... CheckPassFail = "Error"
............ End If
......... End If
...... End If
... End If
End Function