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

**thedutchrudder** · 06-14-2014, 11:46 AM

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!

**oeldere** · 06-14-2014, 12:38 PM

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.

**thedutchrudder** · 06-15-2014, 03:08 PM

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?