Limiting an Array range in =LARGE formula

    Hello - Long time lurker, second time poster.


    I am trying to see if I can limit an array range when LARGE asks for the array based on a header and column categories; returning Vendor Name / Feb Amount / Δ% for the highest Δ% in category selected by drop-down.


    I've attached testing file. Options I've considered -

    • Do I need to make source data a Table?
    • Is there a better way to arrange source data to make formula's easier?

    Any help would be appreciated.


    Thank you,
    -Kyle Rupp

    Hi


    I can't upload the workbook now. I will upload the workbook later today.


    Follow the below steps:


    Create three ranged names as below:


    Use below formula against each name


    [F]
    tblData: Sheet1!$C$1:INDEX(Sheet1!$C$1:$G$1000,COUNTA(Sheet1!$C:$C),5)


    rngCat : =INDEX(tblData,0,2)


    rngPercent =OFFSET(Sheet1!$C$1,MATCH(Sheet1!$J$1,rngCat,0)-1,4,COUNTIF(rngCat,Sheet1!$J$1))
    [/F]


    Then create the following formula on your sample sheet
    L21 - enter as an array formula (CTRL+SHIFT+Enter]


    [F]


    N21 = LARGE(rngPercent,$K21)


    L21 = OFFSET(rngPercent,MATCH(N21,rngPercent,0)-1,-4)


    M21= VLOOKUP(L21,tblData,MATCH($M$20,$C$1:$G$1,0),FALSE)


    [/F]



    Regards


    Maqbool

    Hello,


    Attached is your Test file with the Array Formula ...:wink:


    You do not need to modify anything ... in order to get the formula working ... :smile:


    Hope this is line with your expectations ...

    Carim & Maqbool -


    Thank you both for your help. I've gained a lot of insight from your answers. As always, the community sincerely appreciates your help!


    Best,
    -Krupp825

    Glad you could solve your problem ...:wink:


    Thanks for both your Thanks ...AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hello Carim -


    I have a question for you on this same topic. I see that the LARGE formula currently does not recognize negative values, and just cuts them out. Is there a way to fix this?


    I slightly modified the original file you provided in the following ways to demonstrate how the LARGE formula is not picking up negative values -

    • Added IFERROR at front of each formula to display " - " upon error; more user friendly.
    • Expanded list to display 9 items (enough to encompass all items in each category)

    Using category SHEEP as an example which holds a total of five items, the formula displays the three with positive values, but not the two items assocaited with negative values. Can we edit the formula to include all items, including ones associated with negative values?


    Thanks!
    -Kyle

    Thanks a lot for your very kind Thanks ..AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login