+ Reply to Thread
Results 1 to 8 of 8

Exclude cell from array if (with large function)

  1. #1
    Registered User
    Join Date
    10-12-2017
    Location
    BELGIUM
    MS-Off Ver
    2013
    Posts
    4

    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

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Exclude cell from array if (with large function)

    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.

  3. #3
    Registered User
    Join Date
    10-12-2017
    Location
    BELGIUM
    MS-Off Ver
    2013
    Posts
    4

    Re: Exclude cell from array if (with large function)

    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

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Exclude cell from array if (with large function)

    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?

  5. #5
    Registered User
    Join Date
    10-12-2017
    Location
    BELGIUM
    MS-Off Ver
    2013
    Posts
    4

    Re: Exclude cell from array if (with large function)

    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

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Exclude cell from array if (with large function)

    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))

  7. #7
    Registered User
    Join Date
    10-12-2017
    Location
    BELGIUM
    MS-Off Ver
    2013
    Posts
    4

    Re: Exclude cell from array if (with large function)

    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 !

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Exclude cell from array if (with large function)

    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.

+ 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. [SOLVED] Using the LARGE function to exclude duplicate values
    By Gregbaron in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-05-2017, 04:05 PM
  2. [SOLVED] Combining array formulas and large function
    By lostest in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-16-2015, 08:32 PM
  3. [SOLVED] Retrieve Data From Cell Based in Array Based on LARGE Function
    By justarandomguy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-13-2014, 02:31 AM
  4. Replies: 5
    Last Post: 07-19-2012, 08:18 PM
  5. How to exclude a cell from array
    By ezhenya in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-02-2010, 04:27 AM
  6. Using large in an array function?
    By simonbrownlow in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 10-22-2007, 08:27 AM

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