Function clen_rnk(bst, w As Range) As Variant

Dim ss(99), clen(99), clen_mx(99), Asbaqya(99), qur3a(99), Asbaqya_cof(99), z(99) As Variant, st, fn As Range

Set fn = [a1000].End(xlUp)
Set st = fn.End(xlUp)

play_N = WorksheetFunction.CountA(Range(st, fn))

  For p = 1 To play_N
    For i = 1 To 3
        clen(i) = Cells(st.Row, bst.Column).Offset(p - 1, i - 4)
          If WorksheetFunction.IsNumber(clen(i)) Then
              clen(i + 3) = clen(i) + (3 - i) * 0.1
          Else
              clen(i + 3) = 0
          End If
        
        If clen_mx(p) < clen(i + 3) Then clen_mx(p) = clen(i + 3)
            
          'Asbaqya
        If clen(i) > "X" Then
            clen(i + 6) = Right(clen(i), Len(clen(i)) - 1)
        Else: clen(i + 6) = 0
        End If
        If Asbaqya(p) < clen(i + 6) Then Asbaqya(p) = clen(i + 6)
    Next i
  mx_qur3a = WorksheetFunction.Max(Range("A:A"))
  qur3a(p) = (mx_qur3a - Cells(st.Row + p - 1, 1).Value) / mx_qur3a / 100
  
  If mx_Asbaqya < Asbaqya(p) Then mx_Asbaqya = Asbaqya(p)
    
  Next p
  
  'calculate each z(p)
   For p = 1 To play_N
      wp = Cells(st.Row - 1 + p, w.Column).Value
    If mx_Asbaqya = clen_mx(p) Then
        Asbaqya_cof(p) = -clen_mx(p) / 4 / wp
    Else: Asbaqya_cof(p) = 0
    End If
    
    z(p) = (clen_mx(p) + (clen(p) / wp)) + 0.00009 + Asbaqya_cof(p) + qur3a(p)
  Next p
  
  'calculate Rank of bst
  s = bst.Row - st.Row + p
  If z(s) < 1 Then clen_rnk = "X": Exit Function
  
  rnk = 1 ' assume
  For p = 1 To play_N
    If z(p) > z(s) Then rnk = rnk + 1
  Next p
      
     clen_rnk = rnk
    
End Function