Solved: Custom Function to Calculate Response Time

[rajkumar]

Hi,

i am trying to write a custom function for response time.


Function RTTAIL(Group, GroupA As Range, GroupB As Range, RT)
GroupA = Array("SFIDA", "MALTA", "DP180F")
GroupB = Array("DC12", "FUJHIN", "OLYMPIA", "XES PSG")
RTTAIL = IF(AND(Group = GroupA,RT>2.00),"Tail", _
IF(AND(Group = GroupB,RT>4.00),"Not a Tail",IF(ISBLANK(RT),"Blank Data","")))
End Function


in this GroupA has three products - SFIDA,MALTA,DP180F and GroupB has four products.

for GroupA the response time need to be within 2 hrs
and for GroupB the response time need to be within 4 hrs

i have a log file which has response time(RT) in a column and Group in a column. and i need to see the RT column and Group column for the prdoucts in GroupA and GroupB then if RT value is greater than the limits of respective group then the function should return a text "Tail", if the RT value is within the Group limits then function should return "Not a Tail".

I have attached a sample of my data herewith. in the sample workbook column G has Group products and column P has the RT value.

Can anyone guide me to complete the function.

Thanks a ton
Raj

[p45cal]

I'm not at all sure I understood correctly, but try this function and see if it more or less gives the results you want, and if it does we can refine it.Function RTTAIL(Group As Range, RT As Range) As String
GroupA = Array("SFIDA", "MALTA", "DP180F")
GroupB = Array("DC12", "FUJHIN", "OLYMPIA", "XES PSG")
For Each thing In GroupA
If thing = Group.Value And RT.Value > 2 Then
RTTAIL = "Tail"
Exit Function
End If
Next thing
For Each thing In GroupB
If thing = Group.Value And RT.Value > 4 Then
RTTAIL = "Not a tail"
Exit Function
End If
Next thing
If IsEmpty(RT) Then RTTAIL = "Blank data"
End Function

[rajkumar]

Dear p45cal,

sorry for not briefing clearly.

For GroupA there are three products SFIDA,MALTA,DP180F and these carry 2 hrs response time ie. the call need to be attended within 2hrs.

If the call is attended after 2hrs from the logged time then it becomes response time tail (RTTAIL).

And similarly for GroupB there are four products DC12,Olympia,Fujhin,XES PSG and these carry 4 hrs response time ie. the call need to be attended within 4hrs.

If the call is attended after 4hrs from the logged time then it becomes response time tail (RTTAIL)


Now my data is like in one column all these products are present(GroupA,GroupB) and in one column the response time(RT) is present.

And i need to check for the group column for the product say DC12 and RT column for response time. As per above DC12 carries 4 hrs response time.

so if that RT value is less than or equal to 4hrs then Not a Tail,
if RT value is greater than 4hrs then it is Tail.

I wanted to use a formula like =RTTAIL(GroupColumn,RTValue)

finally if the RT column is blank then function should return Blank Data

Hope this makes things clear.

I also tired to edit your function but i gave me #Name? error

Please find the edited one below

Function RTTAIL(Group As Range, RT As Range) As String
GroupA = Array("SFIDA", "MALTA", "DP180F")
GroupB = Array("DC12", "FUJHIN", "OLYMPIA", "XES PSG")
For Each thing In GroupA
If thing = Group.Value And RT.Value > 2 Then
RTTAIL = "Tail"
Exit Function
End If
Next thing
For Each thing In GroupB
If thing = Group.Value And RT.Value > 4 Then
RTTAIL = "Tail"
Exit Function
End If
Next thing
For Each thing In GroupA
If thing = Group.Value And RT.Value <= 2 Then
RTTAIL = "Not a Tail"
Exit Function
End If
Next thing
For Each thing In GroupB
If thing = Group.Value And RT.Value <= 4 Then
RTTAIL = "Not a Tail"
Exit Function
End If
Next thing
If IsEmpty(RT) Then RTTAIL = "Blank data"
End Function


Kindly help to resolve

Thanks
Raj

[p45cal]

Try this function called RTTAIL2Function RTTAIL2(Group As Range, RT As Range) As String
If IsEmpty(RT) Then
RTTAIL2 = "Blank data"
Exit Function
End If
GroupA = Array("SFIDA", "MALTA", "DP180F")
GroupB = Array("DC12", "FUJHIN", "OLYMPIA", "XES PSG")
For Each thing In GroupA
If thing = Group.Value Then
If RT.Value > 2 Then
RTTAIL2 = "Tail"
Else
RTTAIL2 = "Not a Tail"
End If
Exit Function
End If
Next thing
For Each thing In GroupB
If thing = Group.Value Then
If RT.Value > 4 Then
RTTAIL2 = "Tail"
Else
RTTAIL2 = "Not a Tail"
End If
Exit Function
End If
Next thing
End Function
and use in a worksheet thus:
in cell BJ2:
=RTTAIL2(G2,P2)

[rajkumar]

Thanks it works perfect as pexpected

cudos

Raj :beerchug:

[p45cal]

Now we know exactly what you were wanting we can have a go at making it a bit more elegant, while at the same time making it easier to adjust for changing needs:Function RTTAIL(Group As Range, RT As Range) As String
If IsEmpty(RT) Then RTTAIL = "Blank data": Exit Function
Select Case Group.Value
Case "SFIDA", "MALTA", "DP180F": Limit = 2
Case "DC12", "FUJHIN", "OLYMPIA", "XES PSG": Limit = 4
Case Else: RTTAIL = Empty: Exit Function
End Select
If RT.Value > Limit Then RTTAIL = "Tail" Else RTTAIL = "Not a Tail"
End Function