Exclude cell from array if (with large function)

Hi there !

I'm facing a big challenge ...

I'm starting with this formula:
=LARGE(X$2274:X$2793,1)
=LARGE(X$2274:X$2793,2)
...


I have 3 arrays to consider:

X174:X693 => quantity for seller 1
X699:X1218 => quantity for seller 2
X1749:X2268 => gap of the evolution of quantity versus last year

In W column, there is the product ID, same for both sellers and so for the gap

so it looks like:

Seller 1
Produtc ID Quantities
1 5
2 300
3 58

Seller 2
Produtc ID Quantities
1 54
2 210
3 75

Evol of gaps (based on data from last year)
Produtc ID Quantities
1 30%
2 110%
3 58%


My goal is to rank the biggest gaps for the same products. But as I don't want to consider small quantities, I'd like to retain only quantity per seller and per product >= 50 in the calculated array.


In my head, it sounds like :

=Large( X$2274:X$2793 - (match (all product ID cells which have as quantity <50 in X$174:X$693 ) in X$2274:X$2793) - (match (all product ID cells which have as quantity <50 in X$174:X$693 ) in X$2274:X$2793), 1)

I've search on the net but cannot find anything... :/

Thank you for you help

Hello and welcome to the forum. It is difficult (at least for me) to wrap my head around how your data looks and the result that you are looking for based on the given layout of post #1.

It would be best for you to create a small, representative sample of your data along with the desired result/s (manually entered) of the formula/s and upload it directly to your post.

You can upload a workbook through Go Advanced > Manage Attachments.

Sorry, I see the attachment button but it doesn't work actually. I post the pic, even if it sounds dirty :/
So in the results, I just expect to rank Gaps of evolution without retaining the small quantities < 30 ( here 29 & 25 )

Capture1.PNG

The third line of post #2 shows how to attach a workbook.

We should not have to re-type your data and a picture oftentimes does not give us everything that we need.

That being said, what cells are you looking for help on? Are you only looking for formulas to produce B26:C27 or do you want something for B17:B20 as well?

I assure you I tried what you said. Problem is, it just open en small empty box, not allowing me to upload the file. Whatever...

I'd like to have the B26:C27 solution
In my mind, it would use =large function but I can't figure out how to do the rest which seems very complicated...

B17 is working fine, as you can see the formula above.

Thanks for that follow

Although I feel like you will be asking for more,

B26 =LARGE(B$17:B$20,A26)
C26 =INDEX(A$17:A$20,MATCH(B26,B$17:B$20,0))

That was already what I proposed in my first post...

I ended by removing the quantities when < 30. It seems that it was not really harmful even if we lose some data at then end...
Now the large function is somehow efficient.

Thanks for trying, have a nice wk !

The formulas suggested in post #6 produced the desired outcome from post #3.

If you believe that they will not work with another example, please create that example (and the desired results) and upload the workbook here.