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!