+ Reply to Thread
Results 1 to 2 of 2

percentage markup on various price ranges

  1. #1
    Registered User
    Join Date
    02-09-2016
    Location
    Oceanside, CA
    MS-Off Ver
    2007
    Posts
    2

    percentage markup on various price ranges

    I am trying to set up a pricing worksheet so that if a price of an item is entered into a specific cell, excel will look for a price range and enter that markup into another specific cell.

    For example: item sells for between 5.01 and 7.00 (two different cells, one 5.01 next to it another cell with 7 then next to it markup cell of 110%;
    item sells for between 251.00 and 500.00 (two different cells, one 251 next to it another cell with 500 then next to it markup cell of 55%;

    So if I have 6.95 entered in my target cell, excel searches and enters 100% in my markup cell
    If I have 345 entered in the same target cell, excel enters 55% in the same mark up cell.

    so as the prices change the markups also change....Thanks

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,867

    Re: percentage markup on various price ranges

    Create a table, where the first column has the LOWEST value for the markup given in the second column. For example

    Lowest Value Markup
    0.00 125%
    5.01 110%
    7.01 100%
    251.00 55%

    For your example of between 5.01 and 7.00 getting a markup of 110%, 7.01 gets a different markup, so it is listed in the first column.

    Then use VLOOKUP in your 'other specific cell' (where you want the markup):

    =VLOOKUP(Cell,Table,2,TRUE)

    like

    =VLOOKUP(A2,$J:$K,2,TRUE)

    Where the cell with the price is A2 (your specific cell), the table you created is in columns J and K (with no other values)
    Bernie Deitrick
    Excel MVP 2000-2010

+ 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. Creating and IF statement using price and percentage ranges
    By jfish07 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2014, 06:34 PM
  2. [SOLVED] Price markup to make a certain profit (not percentage)
    By cfcMalky in forum Excel General
    Replies: 5
    Last Post: 09-30-2014, 06:01 PM
  3. Simple Price Markup Percentage Formula
    By Assurance in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-23-2014, 02:14 PM
  4. Margin/price markup as %
    By andycannon in forum Excel General
    Replies: 5
    Last Post: 06-17-2010, 06:30 AM
  5. Replies: 2
    Last Post: 06-02-2010, 03:34 PM
  6. Price List markup with multiple variables
    By YT2009 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2009, 04:16 PM
  7. Calculating Markup For Retail Price
    By gaspower in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2007, 07:16 PM

Tags for this Thread

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