Results 1 to 3 of 3
Thread: IF formula (XP)

20050905, 07:04 #1
 Join Date
 Apr 2005
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
IF formula (XP)
Can someone pls help. I need to write this IF formula for packaging
If blank , nothing in the cell
if less then 499 units  price is in cell M47
if between 500 and 999 units  price is in cell M48
if between 1000 and 1499 units  price is in cell M49
if between 1500 and 1999 units  price is in cell M50
if between 2000 and 2500 units  price is in cell M51
I've tried it all but it just won't work.
Thanks so much
Caitlin

20050905, 07:27 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: IF formula (XP)
Possibility 1:
Create a table like this:
<table border=1><td></td><td align=center>L</td><td align=center>M</td><td align=center>47</td><td align=right>0</td><td align=right>20</td><td align=center>48</td><td align=right>500</td><td align=right>19</td><td align=center>49</td><td align=right>1000</td><td align=right>18</td><td align=center>50</td><td align=right>1500</td><td align=right>17</td><td align=center>51</td><td align=right>2000</td><td align=right>16</td></table>
Say that the number of units is in cell B1. The formula for the price becomes:
<code>
=IF(B1="","",VLOOKUP(B1,$L$47:$M$51,2))
</code>
Possibility 2:
Say that the number of units is in cell B1. The formula for the price is
<code>
=IF(B1="","",OFFSET($M$47,B1/500,0))
</code>
Although this may seem simpler than the first one, the disadvantage is that it only works if all price bands have equal width (500999, 10001499 etc.)

20050905, 08:09 #3
 Join Date
 Apr 2005
 Posts
 3
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: IF formula (XP)
Possibility 1 works great... Thanks