Help with nested functions / differing formulas based on number size

[bigvegan]

Here's the deal, I'm a total Excel moron. It's very frustrating, I kno
Excel can help me, but I'm far too incompetent to figure out how. I
any of you can help me, You'll have my undying gratitude, plus eithe
some delicious burritos or some good chinese food if you're ever in th
Los Angeles Area, or a few bars of handmade cedar/sage/eucalyptu
scented vegetable oil based soap (yes, Fight Club got me started, no
don't rob liposuction clinics) if you're out of the area (or would jus
prefer it to burritos).

I work for a company selling a product in bulk, and I'm stuck using
calculator to do this right now, so I would be TREMENDOUSLY happy i
someone could figure out how to put it into a formula, where I coul
just enter the number and have the price come out.

Basically, our rates are based on the following, where X is the numbe
of pieces of product:

If X is between 1 and 150, X is multiplied by $2.50, and then added t
$229.95.

If X is between 151 and 300, X is multiplied by $2.35, and then adde
to $229.95

If X is between 301 and 399, X is multiplied by $2.25, and then adde
to $229.95.

If X is between 400 and 500, X is multiplied by 2.25, and then added t
$239.95.

If X is between 501 and 599, X is multiplied by $1.95, and then adde
to $239.95.

If X is greater than 600, X is multiplied by $1.95, and then added t
$249.95.

If someone can help me get this into one formula with nested functions
or something similar and idiot proof, I'll be very grateful.

Either post it in an answer to this thread, or e-mail me a
(e-mail address removed) with the formula and your address, so I kno
where to send the soap.

Thank you in advance for your time!

Joh

 

[Arvi Laanemets]

Hi

With X in cell A1:
=CHOOSE(MATCH(A1,{1,151,301,401,501,601},1),229.95,229.95,229.95,239.95,239.
95,249.95)+A1*CHOOSE(MATCH(A1,{1,151,301,401,501,601},1),2.5,2.35,2.25,2.25,
1.95,1.95)

 

[Andy Brown]

Not entirely clear where your discount breaks are, eg:

If X is between 501 and 599, X is multiplied by $1.95, and then added
to $239.95.

If X is greater than 600, X is multiplied by $1.95, and then added to
$249.95.

, so what happens to 600?

In essence, you could use something like

=IF(A1<151,(A1*2.5)+229.95,
IF(A1<301,(A1*2.35)+229.95,
IF(A1<400,(A1*2.25)+229.95,
IF(A1<501,(A1*2.25)+239.95,
IF(A1<600,(A1*1.95)+239.95,
(A1*1.95)+249.95)))))

Rgds,
Andy

 

[Max]

One way using VLOOKUP

In Sheet1
-------------
Set up the look-up table below in A1:C6

001 2.50 229.95
151 2.35 229.95
301 2.25 229.95
400 2.25 239.95
501 1.95 239.95
600 1.95 249.95

In Sheet2
-------------
A1 is where you will enter the # of units/pieces of the product

Put in B1: =VLOOKUP($A1,Sheet1!$A$1:$C$6,COLUMN(),1)
Copy B1 across to C1

Put in D1: =A1*B1+C1

Format B11 as currency

D1 will return the price you're after ..

 

[Max]

Missed out some clarification ..

In Sheet2
-------------
For the # of units input in A1,

B1 returns the unit price,
C1 returns the "base" price
D1 calculates the total price

The reference table in Sheet1's A1:C6

001 2.50 229.95
151 2.35 229.95
301 2.25 229.95
400 2.25 239.95
501 1.95 239.95
600 1.95 249.95

means / assumes that:

1-150 units, $2.50 per unit, $229.95 base price
151-300 units, $2.35 per unit, $229.95 base price
301-399 units, $2.25 per unit, $229.95 base price
400-500 units, $2.25 per unit, $239.95 base price
500-599 units, $1.95 per unit, $239.95 base price
600 units and more, $1.95 per unit, $249.95 base price