Excel Pricing - help please

[Jamie]

Hello, My question is almost exactly the same as Pauline - rockstars on,
6/27/2007, it concerns price breaks, i.e $100 for orders of 1 - 100, $90 for
orders of 101 - 200 etc etc, however, I have 12 volume price breaks, and
apparently can't use more than 7 'if' commands, can anyone think of a way?


EG. 1-100 units = $1000 per unit
101 - 150 = $950 per unit
151 - 200 = $900 per unit
201 - 250 = $850 per unit

I have 12 breaks!!

Please help

Thanks
Jamie

 

[Bob Phillips]

=num_units*LOOKUP(A1,{0,101,151,201},{1000,950,900,850})

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Kevin B]

A VLOOKUP function would serve you well. The table would look like the
following example, let's say A1:B5:
A B
1 $1000
101 $950
151 $900
201 $850
251 $800

If the value of 212 is in A20 and the formula for unit price is in B20 the
VLOOKUP would look like this:

=VLOOKUP(A20,$A$1:$B$5,2)

Where A20 is the value to locate in the table, $A$1:$B$5 is the actual
location of the table and 2 is the column in the table that contains the
result value.

Hope this helps...

 

[Max]

One way

Assume the sample price reference table is entered in Sheet1's A1:C4 like
this:

1 100 1000
101 150 950
151 200 900
201 250 850

where col A houses the lower limits for each tier in ascending order,
& col C contains the corresponding prices

Then in another sheet,
Assuming quantities to be looked up are in A2 down
Place in B2, copy down:
=IF(OR(A2={"",0}),"",IF(A2>250,"Out-of-range",VLOOKUP(A2,Sheet1!A:C,3)))

Adapt the final upper limit part: .. IF(A2>250,"Out-of-range",..
to suit your actuals

 

[Jamie]

Bob,

I am trying to make a master front sheet with all the pricing on the first
sheet, all of the price breaks are on tabs I tried your way, but can't gather
data from another sheet (the one where my bulk price are).

 

[Jamie]

Kevin,

I'm afraid I don't understand. Perhaps I need to explain a bit further:

I am trying to create a pricing calculator for delivery of freight to
various different countries. Each country's pricing is on a seperate tabbed
sheet.

I have created a 'front sheet' where I want the client to be able to put the
most basic of information, # kilos against a country, and in the cell where
it says total freight cost, I want to insert a calculation where by it
multiplies the number of kilos by the correct tariff on a different tabbed
sheet, returning a value on my front sheet. I can't seem to get the Lookup
to work with gathering information from tabbed sheets.

 

[Jamie]

Bob,

Instead of......... {1000,950,900,850}), can I reference a cell on the same
sheet??