+ Reply to Thread
Results 1 to 5 of 5

Pricing Units Between Intervals

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    1

    Pricing Units Between Intervals

    Hi. This may be a simple formula but I'm having trouble. If I know the unit price for 100 units and 1,000 units, how do I calculate the price for all units in-between? For example, the unit cost for 100 is $12.00 and the unit cost per 1,000 is $5.50. I need to know the unit cost for 762 for example. There are no bracketed fees and it is not a progressive pricing structure...just flat fees. Please let me know if you need additional information.

  2. #2
    Registered User
    Join Date
    12-08-2014
    Location
    Paris
    MS-Off Ver
    2013
    Posts
    3

    Re: Pricing Units Between Intervals

    That's the regression line between two points. Compute
    VNew* (V1000-V100)/(900) + V100*(1-(V1000-V100)/(900))
    to get the price for VNew, while V1000 is your price for 1000 units and V100 for 100 units

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Pricing Units Between Intervals

    In the simplest form, =A1*B1 where one cell has the quantity and the other has the unit price.

    If you have the data in a table, then something like.

    =LOOKUP(qty,qty_band_list,price_band_list)*qty

    Where the various descriptions refer to the data in your sheet.

    You will get more accurate answers by providing a sample sheet to show how your data is laid out.

    edit:-

    If you only want the unit price, and not the total price then you don't need the multipliers.

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Pricing Units Between Intervals

    Quote Originally Posted by CamilleJosion View Post
    That's the regression line between two points. Compute
    VNew* (V1000-V100)/(900) + V100*(1-(V1000-V100)/(900))
    to get the price for VNew, while V1000 is your price for 1000 units and V100 for 100 units
    Nothing like overcomplicating a simple task is there

  5. #5
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Pricing Units Between Intervals

    TREND.jpg
    Use the TREND function as seen in this graphic. (Enter as array. . CTRL+SHFT+ENT) Copy down.
    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to do conditional counting, units within units
    By Butcher1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2016, 01:01 PM
  2. Calculating quantiies from irregular intervals to regular intervals
    By abhi1421 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-15-2014, 06:34 AM
  3. [SOLVED] Trying to compare current pricing and costs with suggested pricing
    By dearnne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2013, 07:01 PM
  4. Pricing Guide/Chart with variable pricing increases
    By HSDesigns in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-23-2013, 04:49 PM
  5. [SOLVED] Calculate Pricing from Start and End Date with different Pricing Weight for Each month
    By xyang06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-06-2013, 02:54 AM
  6. [SOLVED] Calculate the Bonus units according to the quantity of the units bought (Help)
    By mo_naf in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2012, 05:51 PM
  7. [SOLVED] Pricing Grid for t-shirt pricing
    By theprint in forum Excel General
    Replies: 5
    Last Post: 03-19-2012, 12:20 PM

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