• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tier based pricing

J

JoyDave

New Member
Hello, I am sure someone asked this but I can not find the answer...


I have an order form where

if 0-4 are ordered the price is $35

if 5-9 are ordered the price is $30

if 10 or more are ordered the price is $25


I want the customer to put a quantity in cell F48 that would then reflect the cost in cell G48.

Cell e48 also has the unit cost and i would like that to change based on the Quantity as well.


I hope that makes sense. Basically all I want is for my customers to put a quantity # in and then the unit cost and total cost would change.


have several line items like this but I am pretty sure if someone shows me one I can figure out the rest.
 
Welcome to the forum.


One formula you could use in G48 would be:

[pre]
Code: 
=LOOKUP(F48,{0,5,10},{35,30,25})
[/pre]
If the pricing structure gets more complicated or larger then it'd be a good idea to use a lookup table rather than using array constants in the formula. That'll make your worksheet easier to maintain.
 
My question was wrong. I did not explain myself properly but I figured it out based on your information. Here is what I did.

E48=Cost

F48=Quantity

G48=Total


E48 I put =LOOKUP(F48,{0,1,5,10},{30,30,25,20})

F48 (customer puts Quantity)

G48 I put =E48*F48


The cost shows $30 if it is 0 but does not affect the line total until the customer puts in a quantity.This makes it all perfect!!! Thank you!!!
 
You must log in or register to reply here.
Back
Top