IF formulas with more than two possible values

[janet]

How do i create a condition based on another cell with more than two
possibilities?
i.e., column A shows the number of units, and in cell B i need to work
out the total price, but the unit price changes according to how many
units there are
eg >= 100 units, price = £100; >100<201 units, price = £200 (therefore
(100*100) + (x*200)); >=201 units, price = £300 (therefore (100*100) +
(100*200) + (x*300)

Thanks!

 

[Arvi Laanemets]

Hi

=((A1<=100)+(A1<=200)+(A1>200))*100

Arvi Laanemets


How do i create a condition based on another cell with more than two
possibilities?
i.e., column A shows the number of units, and in cell B i need to work
out the total price, but the unit price changes according to how many
units there are
eg >= 100 units, price = £100; >100<201 units, price = £200 (therefore
(100*100) + (x*200)); >=201 units, price = £300 (therefore (100*100) +
(100*200) + (x*300)

Thanks!

 

[David Biddulph]

The syntax is
=IF(condition1,value_if_1_true,IF(condition2,value_if_2_true,,value_if_2_false))Note that if the first condition is <=100 (I assume that the >= is a typo?),then you don't need to include >100 as part of the second condition, as youwouldn't get there unless it were >100.--David Biddulph"janet" <[email protected]> wrote in messagedo i create a condition based on another cell with more than twopossibilities?i.e., column A shows the number of units, and in cell B i need to workout the total price, but the unit price changes according to how manyunits there areeg >= 100 units, price = £100; >100<201 units, price = £200 (therefore(100*100) + (x*200)); >=201 units, price = £300 (therefore (100*100) +(100*200) + (x*300)Thanks!

 

[JLatham]

Can you give us more specific examples, as show the value that would be in
column A, units, and the way you'd arrive at the result manually, along with
the resulting cost you would expect?

It looks to me like the price is going up for quantity purchases, and that's
not the usual business model? And what's the price for units if purchased
quantity is LESS THAN 100?

 

[David Biddulph]

I don't know what happened with the formatting of my previous message.
Also a spurious extra comma crept in. It should be
=IF(condition1,value_if_1_true,IF(condition2,value_if_2_true,value_if_2_false))

 

[Pete_UK]

It's strange to have unit prices going up with increasing orders - why
not just order 3 lots of 100 rather than 300 in one go? Also, the
numbers you have chosen to illustrate your example will make the
formula a bit confusing, but try this in B1:

=IF(A1<=100,A1*100,IF(A1<=200,100*100+(A1-100)*200,100*100+100*200+
(A1-200)*300))

Hope this helps.

Pete

 

[Arvi Laanemets]

On second thought, the right formula will be:

=(A1>0)*(IF(A1<=100,A1,100)*100+(A1>100)*IF(A1<=200,A1-100,100)*200+(A1>200)*(A1-200)*300)


Arvi Laanemets



How do i create a condition based on another cell with more than two
possibilities?
i.e., column A shows the number of units, and in cell B i need to work
out the total price, but the unit price changes according to how many
units there are
eg >= 100 units, price = £100; >100<201 units, price = £200 (therefore
(100*100) + (x*200)); >=201 units, price = £300 (therefore (100*100) +
(100*200) + (x*300)

Thanks!

 

[Bernie Deitrick]

Janet,

Forget compound IF statements - use a table of total costs

For example, enter 0 into cells A2 and B2.

Then in A3, enter 100, and the total cost for 100 units in B3 - say, 10000.

Then do the same for all the 'breakpoints' - quantity in column A, total cost (not incremental per
unit) for that number of units in B.

Then for the number of units in cell D2, use this formula.

=PERCENTILE($B$2:$B$10,PERCENTRANK($A$2:$A$10,D2,3))

HTH,
Bernie
MS Excel MVP


How do i create a condition based on another cell with more than two
possibilities?
i.e., column A shows the number of units, and in cell B i need to work
out the total price, but the unit price changes according to how many
units there are
eg >= 100 units, price = £100; >100<201 units, price = £200 (therefore
(100*100) + (x*200)); >=201 units, price = £300 (therefore (100*100) +
(100*200) + (x*300)

Thanks!

 

[janet]

It's strange to have unit prices going up with increasing orders - why
not just order 3 lots of 100 rather than 300 in one go? Also, the
numbers you have chosen to illustrate your example will make the
formula a bit confusing, but try this in B1:

=IF(A1<=100,A1*100,IF(A1<=200,100*100+(A1-100)*200,100*100+100*200+
(A1-200)*300))

Hope this helps.

Pete




- Show quoted text -

Thanks, this has worked.
For info we just have a slightly complicated contract whereby the
price per unit changes because of different overheads depending how
many units there are

 

[Pete_UK]

Okay, well thanks for feeding back, Janet.

Pete

Thanks, this has worked.
For info we just have a slightly complicated contract whereby the
price per unit changes because of different overheads  depending how
many units there are- Hide quoted text -

- Show quoted text -

 

[JLatham]

Glad you got a solution that worked. I just wanted to perhaps head off
confusion early, but you had accurately described your situation and so
things worked out well.