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!

Hi

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

Arvi Laanemets


"janet" wrote in message
...
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!

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" wrote in do 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!

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?

"janet" wrote:

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!

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))
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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 you wouldn't get there unless it were 100.

"janet" wrote in message
...
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 workout
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!

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

On Oct 1, 12:40*pm, janet wrote:
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!

On second thought, the right formula will be:

=(A10)*(IF(A1<=100,A1,100)*100+(A1100)*IF(A1<=20 0,A1-100,100)*200+(A1200)*(A1-200)*300)


Arvi Laanemets



"janet" wrote in message
...
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,

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


"janet" wrote in message
...
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!

On 1 Oct, 13:15, Pete_UK wrote:
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

On Oct 1, 12:40*pm, janet wrote:



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!- Hide quoted text -

- 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

Okay, well thanks for feeding back, Janet.

Pete

On Oct 1, 5:05*pm, janet wrote:
On 1 Oct, 13:15, Pete_UK wrote:





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

On Oct 1, 12:40*pm, janet wrote:

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!- Hide quoted text -

- 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- Hide quoted text -

- Show quoted text -

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.

"janet" wrote:

On 1 Oct, 13:15, Pete_UK wrote:
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

On Oct 1, 12:40 pm, janet wrote:



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!- Hide quoted text -

- 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