1. ## 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

2. ## 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 (500-999, 1000-1499 etc.)

3. ## Re: IF formula (XP)

Possibility 1 works great... Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•