+ Reply to Thread
Results 1 to 4 of 4

Need macro/formula to only show rows that have values above a benchmark

  1. #1
    Registered User
    Join Date
    06-14-2014
    Posts
    5

    Need macro/formula to only show rows that have values above a benchmark

    Hello everyone,

    I'm quite the novice when it comes to macros/formulas, so I need your help for this one.
    I created a benchmark of figures on product sales of a shop: (average price per product, average # of sales per product).
    I have a huge list of products with their price and how many times they have been sold in our shop.
    Now i'd like to have another list show up in a seperate tab with ONLY the products (+data on price and sales) that have ONE OF their values above or equal to the benchmark.

    i.e.
    Benchmark:
    Avg. price: 10
    Avg. sales per product: 4

    Products
    product, price, sales
    a 4 2
    b 11 6
    c 3 14

    So here I'd like to have a list come up that shows (and thus filters out product a) :

    b 11 6
    c 3 14

    I hope you can help me... Thank you in advance!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Need macro/formula to only show rows that have values above a benchmark

    d2
    Please Login or Register  to view this content.
    and drag down.

    in which b1 = 10 and c1 = 4 (your average)

    Then you can filter on column D.

    Since you don't add an excel file, I can't show it to you.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-14-2014
    Posts
    5

    Re: Need macro/formula to only show rows that have values above a benchmark

    Thanks for you answer. This is already the direction into which I want to go.

    However, when I use this formula: it just gives me the 'yes' or 'no' for each product.
    Is there a possibility to instead filter out all the 'no' and get the actual values for all the yes's?

    I have attached an excel file. Maybe you can give it a try there?

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,988

    Re: Need macro/formula to only show rows that have values above a benchmark

    You can filter on YES instead of NO.

    See the attached file.

+ 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. Macro to Copy Paste Rows Depending on a Cell Value and show values against each row
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-29-2013, 11:41 AM
  2. Need help to show/hide rows based on values from a cell
    By concurmgr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2012, 02:56 PM
  3. Flagging up formula result beyond set benchmark
    By Michael Pendragon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2012, 04:22 PM
  4. Macros to show unique rows with largest values.
    By undergraduate in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2010, 06:59 PM
  5. Do not show rows with no values in Pivot Table
    By Mark in forum Excel General
    Replies: 0
    Last Post: 04-19-2005, 02:06 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