Nested IF AND formula for a shorter one

[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!!

 

[Arvi Laanemets]

=((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!!

 

[Lars-Åke Aspelin]

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

 

[Rick Rothstein]

Here is one more for you to consider...

=10*(1+TEXT((A1>3)*(A1-3)/2,"0"))

 

[Bob I]

Personally, I would question the pricing scheme in the first place.

 

[1234]

Thanks to all of you. That worked great!!