Hello,
I suggest to use:
=pass_or_fail("MUSTNOTFAIL",E1,G1,I1,K1,"MUSTPASS",M1,O1,Q1,"MUSTPASS",S1,U1)
You will have to put the code shown below into a VBA module (push ALT +
F9, insert a module and paste the code shown below):
Option Explicit
Const spasschar As String = "ABCD"
Const sfailchar As String = "E"
Function pass_or_fail(ParamArray v() As Variant) As Boolean
'Call with
=pass_or_fail("MUSTNOTFAIL",E1,G1,I1,K1,"MUSTPASS",M1,O1,Q1,"MUSTPASS",S1,U1)
'to solve newsgroup question shown below.
'From: Zainuddin Zakaria - view profile
'Date: Sun, Sep 24 2006 4:23 pm
'Email: "Zainuddin Zakaria" <
[email protected]>
'Groups: microsoft.public.Excel
'
'
'Please help me with a formula for my marksheet.
'
'The exam grading is A, B, C, D and E. E is a failure
'
'
'To pass the overall exam, a student must :-
'
'
'Must NOT FAIL in any 4 subjects from columns E7, G7, I7 and K7
'and MUST PASS ONE subject from column M7, O7, or Q7
'and MUST PASS ONE subject from column S7, or U7
'
'
'What is the best formula for this?
Dim i As Long, j As Long
Dim bpass As Boolean
Dim r As Range
Dim vi As Variant
Dim s As String
s = "Error"
For Each vi In v
Select Case TypeName(vi)
Case "String"
If s = "MUSTPASS" And Not bpass Then
pass_or_fail = False
Exit Function
End If
s = vi
If s <> "MUSTPASS" And s <> "MUSTNOTFAIL" Then
pass_or_fail = CVErr(xlErrValue)
Exit Function
End If
bpass = False
Case "Range"
If s = "MUSTNOTFAIL" Then
For Each r In vi
If InStr(sfailchar, r) > 0 And Len(r) > 0 Then
pass_or_fail = False
Exit Function
End If
Next r
ElseIf s = "MUSTPASS" Then
For Each r In vi
If InStr(spasschar, r) > 0 And Len(r) > 0 Then
bpass = True
End If
Next r
Else
pass_or_fail = CVErr(xlErrValue)
Exit Function
End If
Case Else
pass_or_fail = CVErr(xlErrRef)
Exit Function
End Select
Next vi
If s = "MUSTPASS" And Not bpass Then
pass_or_fail = False
Exit Function
End If
pass_or_fail = True
End Function
