function help

[Imran Azam]

Hi Guys

i am struggling with a function I am trying to create , have some data as shown below on and excel sheet ( start from column A3)

Group name	total Mark	average student mark	overall
A	100​	80%​	High
B	80​	65%​	High
C	19​	14%​	Low
D	50​	60%​	None
E	10​	5%​	Low

i need to write a function which would allow me to input a group name and then return a message based on the rules below

• If the inputted Group name doesn't exist in the data then return the message 'Group does not exist”
• If the total mark of the inputted Group name is less than 20 but more than 10 OR the average student mark is less than 15% OR the overall is "low" then return the message " Group X is not ok" X being the name the group
• If the total mark of the inputted planet is less than 60 but more than 20 AND average student mark is AT LEAST 60% AND overall is "None" then return the message " Group X is ok" X being the name the group
• otherwise return message ' Group X is normal'

can anyone help me create this

 

[offthelip]

I have assumed that data you presented is on Sheet1, Here is afunctions to do what you asked. I hope I got the equations correct:

Function groupcat(groupname As Variant) As String
With Worksheets("sheet1")
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    inarr = .Range(.Cells(1, 1), .Cells(lastrow, 4))
End With
 notfnd = True
 For i = 3 To UBound(inarr, 1)
   If inarr(i, 1) = groupname Then
     notfnd = False
       groupcat = "Group " & groupname & " is normal"
      If (inarr(i, 2) < 20 And inarr(i, 2) > 10) Or inarr(i, 3) < 15 Or inarr(i, 4) = "Low" Then
       groupcat = "Group " & groupname & " is not ok"
      End If
      If (inarr(i, 2) < 60 And inarr(i, 2) > 20) And inarr(i, 3) > 60 And inarr(i, 4) = "None" Then
       groupcat = "Group " & groupname & " is ok"
      End If
      Exit For
   End If
 Next i
 If notfnd Then
       groupcat = "Group does not exist"
 End If
End Function