Assign Price Tiers using if Statements?

[baindes]

Hello,

I am trying to assign price tiers to Column A that has an average price per unit. I have 6 tiers:
.50 and below
.51-.99
1.00-1.24
1.25-1.49
1.50-1.70
1.80 and above.

How do I write a formula in column b to nest all of these?

I started with if(a2<=.50,"tier 1",if(a2 between .51-.99, "tier 2"...etc. Thanks for the help!

 

[crook_101]

Hi,

Try:

=IF(A2<0.51,"Tier 1",IF(A2<0.99,"Tier 2",IF(A2<1.24,"Tier 3",IF(A2<1.49,"Tier 4",IF(A2<1.7,"Tier 5",IF(A2<1.8,"Tier 6","Tier 7"))))))

I have taken liberty of guessing what happens if value is between 1.7 and 1.8 as this is not defined in your post.

Post back if you need it ammending.

Regards,
Ian

 

[Andrew Poulsom]

Make a table like this:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num>0.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Tier1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>0.51</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tier2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1.00</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tier3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1.25</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tier4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1.50</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tier5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1.80</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Tier6</TD></TR></TBODY></TABLE>

If that's in B1:C6, you can use this formula:

=VLOOKUP(A2,B1:C6,2)

 

[baindes]

Hi Thank you for the reply. I'm sorry Tier 5 is 1.50-1.79 then 1.80+. I put your formula in and all I get is Tier 7 for everything?

 

[baindes]

Thanks Andrew,

The lookup table only returns the right tier if it is an exact match. I am looking up the value of example .51 and between .99 then Tier 2. Am I doing something wrong?

 

[Andrew Poulsom]

It's not an exact match lookup. For example, for 0.84 the formula (correctly) returns Tier2.

 

[baindes]

Thank you Andrew, I made a mistake. It does work but I don't understand how? Isn't Vlookup to find and exact match?

Thank you I appreciate it.

 

[baindes]

Ahhhh, I just saw it. There is not True or False. This is great! Thanks again.

 

[Andrew Poulsom]

VLOOKUP only looks for an exact match if its fourth argument is FALSE. If TRUE or omitted (as I have done) an approximate match is returned.