Search This Blog

Friday, June 26, 2015

Excel looking group interval or frequency (formula an VBA Macro)




Excel Formula to looking group of interval data 
=IF(AND(IF(ISNUMBER(A1)=FALSE,0,A1)<=80,A2>80),-1,0)





Total minutes Status dengan formula

Group
85 -1
==> Group 1
30 0


89 -1
==> Group 2
89 0

80 0


30 0


89 -1
==> Group 3
89 0


30 0


60 0


89 -1
==> Group 4
99 0

89 0

86 0

80 0


60 0


89 -1
==> Group 5
81 0

60 0


89 -1
==> Group 6
81 0






There are six (6) group of data interval which has value > 80


I'm creating vba function as well to looking the Group Frequency in Excel


Function cariinterval(sel1 As String, sel2 As Single) As Single


Dim vsel1 As Single

If IsNumeric(sel1) = False Then
    vsel1 = 0
    Else
    vsel1 = sel1
End If

If vsel1 <= 80 And sel2 > 80 Then
    cariinterval = -1
    Else
    cariinterval = 0
    End If




End Function





No comments:

Post a Comment