Making tiered list with AND statement?

[normalkiwi]

Hi all,

I am trying to make a tiered list, or rank, to rank employees success. There are 2 things I am looking for in the rankings, number of sales that day and the conversion rate.

Tier 1: Sales greater or equal to 30 and 100% conversion Output: 1
Tier 2: Sales greater or equal to 25 and 99%-95% conversion. Output: 2
Tier 3: Sales greater or equal to 20 and 94%-90% conversion. Output: 3
Tier 4: whatever is leftover Output: 4

I am having trouble if this is something I can do with a IF/AND formula? If so, what does something like this look like?

Thanks

 

[ranman256]

somethin like this?
sheet DATA has your data listed

sheet RATES has the tier values:
tierLevel sales ConvStart ConvEnd
Tier1 30 100% 100%
Tier2 25 95% 99%
Tier3 20 90% 94%
Tier4 0

run: AnalyzeTier()

'Tier 1: Sales greater or equal to 30 and 100% conversion Output: 1
'Tier 2: Sales greater or equal to 25 and 99%-95% conversion. Output: 2
'Tier 3: Sales greater or equal to 20 and 94%-90% conversion. Output: 3
'Tier 4: whatever is leftover Output: 4
Sub AnalyzeTier()
Dim aTiers()
Dim bTierFound As Boolean
Dim t As Integer, iRows As Integer, r As Integer
Dim vConv, vSaleAmt, vTierAward, vName
  '==== load rates
Sheets("rates").Activate
Range("a1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
ReDim aTiers(iRows - 1, 3)
Range("a2").Select
r = 1
While ActiveCell.Value <> ""
    aTiers(r, 1) = ActiveCell.Offset(0, 2).Value  'convStart
    aTiers(r, 2) = ActiveCell.Offset(0, 3).Value  'convEnd
    aTiers(r, 3) = ActiveCell.Offset(0, 1).Value  'sales
  
    ActiveCell.Offset(1, 0).Select   'next person row
    r = r + 1
Wend
iRows = iRows - 1
 '======process data
Sheets("Data").Activate
Range("B2").Select

While ActiveCell.Value <> ""
    vSaleAmt = ActiveCell.Offset(0, 1).Value
    vConv = ActiveCell.Offset(0, 3).Value
    vName = ActiveCell.Offset(0, 0).Value
  
    t = 1
    bTierFound = False
    While Not bTierFound And t <= iRows
        For t = 1 To iRows
            If vConv >= aTiers(t, 1) And vConv <= aTiers(t, 2) And vSaleAmt >= aTiers(t, 3) Then
              bTierFound = True
              vTierAward = "Tier" & t
              Exit For
            End If
        Next
    Wend
  
  
    If bTierFound Then
    Else
      vTierAward = "Tier" & iRows
    End If
  
    ActiveCell.Offset(0, 4).Value = vTierAward
  
    ActiveCell.Offset(1, 0).Select   'next person row
Wend
End Sub
'If vName = "Mad Hatter" Then
'Beep
'End If

 

[jtakw]

Hi,

Your written requirements are unclear.
What if sales is 21 at 96% Conversion...

Does this achieve what you want.

Book3.xlsx
	B	C	D	E	F	G
1
2			# Sales		Conversion	Tier
3			22		100%	3
4			14		98%	4
5			11		97%	4
6			6		65%	4
7			25		100%	2
8			17		85%	4
9			30		96%	2
10			30		100%	1
Sheet1040

Cell Formulas
Range	Formula
G3:G10	G3	=IFERROR(LOOKUP(2,1/((D3>={20,25,30})*(F3>={0.9,0.95,1})),{3,2,1}),4)