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

=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)

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

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...
--
Kevin Backmann


"Jamie" wrote:

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

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(A2250,"Out-of-range",VLOOKUP(A2,Sheet1!A:C,3)))

Adapt the final upper limit part: .. IF(A2250,"Out-of-range",..
to suit your actuals
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jamie" wrote:
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,

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).


"Bob Phillips" wrote:

=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)

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

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.

"Kevin B" wrote:

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...
--
Kevin Backmann


"Jamie" wrote:

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,

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

"Bob Phillips" wrote:

=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)

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