I need help in Excel on pricing.

[paulina_rockstar]

PLEASE HELP!!

I am trying to do tier pricing on a worksheet. This is the help that I
need. If someone orders 5-50 boxes the price will be $50 per box, 51-99
boxes the price will be $48 per box, 100+ boxes will be $46 per box. Can
someone please help me.

I would be very grateful.

 

[Gary''s Student]

If the quantity is in A1,then:

=IF(A1<50,50,IF(A1<100,48,46))*A1


is the total cost

 

[Farhad]

Hi,

Try this:

in the cell B2 inter this formula:

=IF(AND(A1>4,A1<51),50,IF(AND(A1>50,A1<100),48,IF(A1>99,46)))
so if you enter the order in the cell A1 then you can find appropriate price
in cell B2.

Thanks,

 

[driller]

Do you accept orders less than 5 boxes?

regards,
driller

 

[paulina_rockstar]

Thanks Gary! I really appreciate it. You're the greatest!!!

 

[paulina_rockstar]

No we don't except orders less than 5 boxes at wholesale prices.

 

[paulina_rockstar]

I have another question. How do I protect the formula in the cell. I don't
want anyone to screw it up!

 

[Roger Govier]

Hi Paulina

i think driller was trying to point out that as it stands, Gary's
formula would charge 50 for orders of 1,2,3 and 4.
Either put another condition in, using your retail price - say 80, for
amounts less than 5 or go with Farhads solution

=IF(A1<5,80,IF(A1<50,50,IF(A1<100,48,46)))*A1

As far as Protecting the formula is concerned, all cells are locked by
default, and as soon as you apply Password protection to the sheet the
cell becomes locked.
What you will nedd to do, is mark the range of cells where you want
users to be able to enter data, then
Format>Cells>Protection>remove the check mark from Locked.

Tools>Protection>Protect Sheet You need not bother to give a password
if you don't want to. If you do, be sure to write it down somewhere.

Be aware that even with a password, you cannot really protect a sheet
from someone changing things if they are determined.
Also, be aware that password protection does prevent the use of some
other functions.