Nested IF AND formula for a shorter one

  • Thread starter Thread starter 1234
  • Start date Start date
1

1234

Hi,

I want a formula that shows:

If I buy between 1 and 3 articles price is $10
If I buy between 4 and 5 articles price is $20
If I buy between 6 and 7 articles price is $30
If I buy 8 articles price is $40

But I don´t want 4 IF AND functions. Is there a shorter formula?

Thanks in advance!!
 
=((A1>=1)+(A1>=4)+(A1>=6)+(A1>=8))*10

Arvi Laanemets


Hi,

I want a formula that shows:

If I buy between 1 and 3 articles price is $10
If I buy between 4 and 5 articles price is $20
If I buy between 6 and 7 articles price is $30
If I buy 8 articles price is $40

But I don´t want 4 IF AND functions. Is there a shorter formula?

Thanks in advance!!
 
Hi,

I want a formula that shows:

If I buy between 1 and 3 articles price is $10
If I buy between 4 and 5 articles price is $20
If I buy between 6 and 7 articles price is $30
If I buy 8 articles price is $40

But I don´t want 4 IF AND functions. Is there a shorter formula?

Thanks in advance!!

Assuming that the number of articles is an integer in cell A1,
here are two alternatives that you may try:

=CHOOSE(A1,10,10,10,20,20,30,30,40)

=LOOKUP(A1,{1,4,6,8},{10,20,30,40})

Hope this helps / Lars-Åke
 
Back
Top