+ Reply to Thread
Results 1 to 4 of 4

Pricing Sheet - If xx quantity, use this price

  1. #1
    Registered User
    Join Date
    10-31-2006
    Posts
    1

    Pricing Sheet - If xx quantity, use this price

    Hi Folks,

    I am trying to create a staggered pricing model in excel and need a little leg up.

    I want to enter the quantity into one cell and have a formula pull the price from a two-column table like below and return the price into a "total' cell. Seems easy enough, though I can't figure out how to make it differentiate the quantities and prices. Any help would be appreciated.

    0-5000 $1.00
    5000-6000 $0.90
    6000-7000 $0.80
    7000-8000 $0.70
    8000-9000 $0.60
    9000-10,000 $0.50
    10,000-50,000 $0.40
    50,000-100,000 $0.30
    100,000-1,000,000 $0.20
    1,000,000+ $0.10

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Hendy_100
    Hi Folks,

    I am trying to create a staggered pricing model in excel and need a little leg up.

    I want to enter the quantity into one cell and have a formula pull the price from a two-column table like below and return the price into a "total' cell. Seems easy enough, though I can't figure out how to make it differentiate the quantities and prices. Any help would be appreciated.

    0-5000 $1.00
    5000-6000 $0.90
    6000-7000 $0.80
    7000-8000 $0.70
    8000-9000 $0.60
    9000-10,000 $0.50
    10,000-50,000 $0.40
    50,000-100,000 $0.30
    100,000-1,000,000 $0.20
    1,000,000+ $0.10
    Is this weeks homework on Lookup tables?

    try http://www.excelforum.com/attachment...4&d=1162280508 from the post at http://www.excelforum.com/showthread.php?t=579258, this should explain how to do that.

    hth
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,707

    UDF Price per QTY

    Here's a function you could use, check out the example attatched



    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-23-2004
    Location
    Houston, TX
    Posts
    571
    Quote Originally Posted by Hendy_100
    Hi Folks,

    I am trying to create a staggered pricing model in excel and need a little leg up.

    I want to enter the quantity into one cell and have a formula pull the price from a two-column table like below and return the price into a "total' cell. Seems easy enough, though I can't figure out how to make it differentiate the quantities and prices. Any help would be appreciated.

    0-5000 $1.00
    5000-6000 $0.90
    6000-7000 $0.80
    7000-8000 $0.70
    8000-9000 $0.60
    9000-10,000 $0.50
    10,000-50,000 $0.40
    50,000-100,000 $0.30
    100,000-1,000,000 $0.20
    1,000,000+ $0.10

    You can do this:

    Enter the following from Cells A1 to A10:

    0
    5000
    6000
    7000
    8000
    9000
    10000
    50000
    100000
    1000000

    On the corresponding cells in column B (B1 to B10), enter the following:

    1
    0.9
    0.8
    0.7
    0.6
    0.5
    0.4
    0.3
    0.2
    0.1

    You can use this formula then to pull out the price that you require:

    =vlookup(C1,$A$1:$B$10,2,2)

    where C1 is where you enter the quantity.

    If you want the total price based on the quantity, then use this formula:

    =C1*(vlookup(C1,$A$1:$B$10,2,2))

    Hope this helps you.

    Regards.
    BenjieLop
    Houston, TX

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1