Using the IF function with a range of numbers

I suggest using a different method which involves adding a second table that contains the threshold/discount pairs. This makes it easier to adjust later:

I added the table "Discount" (on the right). The first row is a header in BOTH tables.

The table on the left is set up as follows:

C2=IFERROR(VLOOKUP(B2,Discount :: A:B, 2, 1), 0)

select C2 and fill down as needed

Ryan,

I did not present anything with regard to computing the cost. If the you have several items that change cost regularly you could add yet another table for the cost of items:

I called the new table "Global Information". Then I inserted two new columns (one for "Item" and one for "QTY")

D2=IFERROR(VLOOKUP(B2,Global Information :: A:B, 2, 0), 0)*C2

E2=IFERROR(VLOOKUP(D2,Discount :: A:B, 2, 1), 0)

select D2 and E2 and fill down

Ryan,

Here is another variation of what I previously presented with your specific ranges. Please note this can be done without a lookup table but the translation from amount spent to gift card amount is NOT linear. I can determine an approximation for the function that would perform this translation but my opinion is the lookup is more straight forward:

The example takes input from the "Line Cost" column and looks up that value int he "Gift Card" table and returns the value from the 2nd column in the row that is less than the "Line Cost".

I hope this helps

HI Ryan,

In this post you have, except for the issue noted by Badunit, the basis for the discount table described by Wayne:

I'm trying to devise a way of knowing what Cash Card a customer will receive depending on the amount of the purchase, based on the following table:

$0 to $999 purchase = $50 Cash Card

$1,000 to $1,999 purchase = $100 Cash Card

$2,000 to $2,999 purchase = $200 Cash Card

$3,000 to $3,999 purchase = $300 Cash Card

$4,000 to $4,999 purchase = $400 Cash Card

$5,000 to $5,999 purchase = $550 Cash Card

$6,000 to $6,999 purchase = $700 Cash Card

$7,000 to $7,999 purchase = $850 Cash Card

$8,000 to $8,999 purchase = $1,000 Cash Card

$9,000 to $9,999 purchase = $1,200 Cash Card

$10,000 to UP purchase = $1,500 Cash Card

To take care of the issue noted by Badunit, you need to revise the top line to state the minimum purchase necessary to earn a cash card, then add a line above that to handle purchases under that amount. I've arbitrarily chosen a $500 minimum, which results in these two lines:

$0 to $499 purchase = $0 Cash Card

$500 to $999 purchase = $50 Cash Card

To write this as Wayne's table, you need to remove all of the text (including the $ currency markers), the top end of each range, and the = sign. What you're left with is two columns of numbers: The bottom number of each range of values, and the number telling the dollar amount of the cash card for that range. Here's your table, edited to make it work with LOOKUP:

Here is a second table (Main), containing only the labels and the two cells involved in the calculation of the cash card amount. The Purchase amount is the amount used in your example. The Cash Card amount is produced by the LOOKUP formula below the image.

Purchase price is in cell B1 of the table "Main". The LOOKUP formula is in B2:

B2: =LOOKUP(B1,CC Amt :: A,CC Amt :: B)

LOOKUP acts essentially as you request in your post containing the iPad image with the time shown as 1:46. But it takes a much simpler approach.

Here's the syntax:

LOOKUP(search-for,search-where,return-from)

LOOKUP gets the search-for value (2535) from cell B1.

It looks in column A of the table "CC Amt" for the largest value less than or equal to the search-for value, and finds 2000.

It returns the value from the same line of column B (200).

Below are two more examples. Main-2 and Main-3 are copies of the Main table. Both contain exactly the same formula as Main, but have had a different Purchase price amount entered. LOOKUP uses the same CC Amt table as is used above, and returns the appropriate Cash Card value for each of the two new amounts:

You may, as implied in your 1:46 post, use a pair of cells to calculate the Cash Card amount for each widget in the purchase, then total the Cash Card amounts, or you may use a single pair of cells, one containing the total purchase amount, the other containing the Cash Card amount due for that total. That choice is yours. The formula is the same for either case.

Regards,

Barry

Another solution that assumes the pattern you gave continues:

$0-999 = $0

$1000-1999 = $100

$2000-2999 = $150 ($50 more than the previous category)

$3000-3999 = $225 ($75 more than the previous category)

$4000-4999 = $325 ($100 more than the previous category)

etc

Discount =IF(B<1000,0,12.5*(INT(B/1000))^2+12.5*INT(B/1000)+75) where the value is in B

Of course, at around $40,000 it becomes a 50% discount and at $79,000 and above the discount will exceed the price.

If you provide more info on the discount, maybe a better formula can be constructed. Or maybe you should reconsider the steepness of the discount curve or apply a simple percentage instead.

This formula will give the cash card value:

=IFERROR(CHOOSE(MIN(INT(A1/1000), 10)+1, 50, 100, 200, 300, 400, 550, 700, 850, 1000, 1200, 1500), "")

A1 is the cell where the gross amount is entered. Substitute your actual cell number there.

Jerry

Using an entire table to do the same thing as what can be done in a simple single-cell formula (such as Jerry provided) seems inefficient. You would be better served by de-boggling your mind and trying to understand the formula versus using a very inefficient approach.

You might want to reconsider your first tier for the gift card/cash card. You have it as $0-$999 = $50 card. As an astute customer, I'd buy a bunch of $1 items one at a time and receive $50 gift cards for each purchase. Quite a deal!

Ryan,

Here's a solution with no auxiliary table:

Discount Price expression:

=IFERROR(A-CHOOSE(INT(A/1000)+1, 0, 100, 150, 225), "and so on")

Regards,

Jerry

Ryan,

Could you say more about the layout of your document or could you post a screen shot of a mock-up?

Your responses to Wayne have me wondering if we have the full story.

Jerry

Ryan,

I give up on this moving target question.

Jerry