IF statement to return different values for 3+ conditions

Question

I'm trying to configure a volume discount system in a cell. I want to say:

If qty is 1, cost is $49.99; if qty is 2-3, cost is $49.50; if qty is 4+, cost is $48.99.

The furthest I've gotten is:

=OR((IF(I7<2,49.99)),(IF(OR(I7>1,I7<4),49.5)))

in which I7 is the cell containing qty. This returns TRUE which I understand is how the OR function works.

I'm just at a loss to how I can incorporate multiple outputs into a single cell given multiple potential conditions.

Rick Hitchcock · Accepted Answer · 2015-09-23T14:57:23.207

5

You can omit OR and simply chain multiple IFs:

=IF(I7<=0, 0, IF(I7=1, 49.99, IF(I7<4, 49.5, 48.99)))

In pseudo-code, this reads:

If      I7 <= 0, then return  0.00
else If I7 =  1, then return 49.99
else If I7 <  4, then return 49.50
else                  return 48.99

edited Sep 23 '15 at 14:57

answered Sep 23 '15 at 13:55

Rick Hitchcock

35,202
5
48
79

Ok I'd tried nested IF's before, but I guess I'd made a mistake so thanks. But I want to be more specific. With your formula, values in `I7` that are =-0 return `49.50`. Here's my adjusted formula, but for values 2 and 3, it returns 0... `=IF(I7=1, 49.99, IF(13, 48.99, 0)))` – Willman Sep 23 '15 at 14:39
1

You can't use `1 – Rick Hitchcock Sep 23 '15 at 14:45
Ok thanks. I used the `AND` formula for handling negative numbers as well. – Willman Sep 23 '15 at 14:50
1

In that case, the first test should be `I7<=0`. Updated my answer. – Rick Hitchcock Sep 23 '15 at 14:51
Oh ok, yeah that's less clutter :) – Willman Sep 23 '15 at 14:52

score 1 · Answer 2 · answered Sep 23 '15 at 18:01

1

A conventional alternative to nested IFs is to use a sorted lookup_array for breakpoints only and apply VLOOKUP with the inexact match option. Say you have a range (if of Workbook Scope, anywhere in the workbook) named table_array:

| 1 | 49.99 |  
| 2 | 49.5  |  
| 4 | 48.99 |

and E1 contains the quantity:

=VLOOKUP(E1,table_array,2,1)

answered Sep 23 '15 at 18:01

pnuts

58,317
11
87
139

1

I actually thought about using `VLOOKUP` but my data wasn't sorted. I didn't think about having a separate table of values off to the side though. Thanks for the extra input! – Willman Sep 25 '15 at 12:22

IF statement to return different values for 3+ conditions

2 Answers2

Linked