Consulting

Results 1 to 6 of 6

Thread: Solved: Custom Function to Calculate Response Time

  1. #1

    Solved: Custom Function to Calculate Response Time

    Hi,

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

    [VBA]
    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
    [/VBA]

    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

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.[VBA]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
    [/VBA]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3

    Custom Function to Calculate Response Time

    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
    [VBA]
    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
    [/VBA]

    Kindly help to resolve

    Thanks
    Raj

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Try this function called RTTAIL2[vba]Function 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
    [/vba]and use in a worksheet thus:
    in cell BJ2:
    =RTTAIL2(G2,P2)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5

    Custom Function to Calculate Response Time

    Thanks it works perfect as pexpected

    cudos

    Raj

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:[vba]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
    [/vba]
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •