Results 1 to 4 of 4

Thread: Can someone please help me understand a formula from an ExcelisFun video?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can someone please help me understand a formula from an ExcelisFun video?

    He goes over a little too fast for my newbie ears:



    I managed to adapt the formula to extract data from non adjacent COLUMN(S), but I don't understand the following:


    1. What part the AGGREGATE and SMALL functions play?
    2. Why he has to subtract the second ROW reference and divide?
    3. What the <> operator means
    4. What part the ROWS reference plays
    5. Not too concerned about the IF portion since it's just to remove the NUM errors, but more curious about the Index on wards




    The formula: =IF(ROWS(D$2:D2)>COUNTA($A$2:$A$11),"",INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/($A$2:$A$11<>""),ROWS(D$2:D2))))


    The video: https://www.youtube.com/watch?v=cMchVJe7TDs




    thanks!

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,987
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can someone please help me understand a formula from an ExcelisFun video?

    Welcome to the forum.

    1. AGGREGATE with 15 as the function_num argument is the equivalent of SMALL. But AGGREGATE is superior to SMALL here because of the options argument, which is set here at 6, or Ignore errors. That is important because the row_num argument in INDEX is intentionally meant to create errors, which AGGREAGTE will filter out.
    2. The ROW(entire range)-ROW(first row)+1 thing is meant to determine how many rows are being assessed. But of course, it's then divided by a 1 or 0 depending on whether the cell is blank or not blank. See next step.
    3. The <> means not equal to. Here it is used to create the aforementioned error that AGGREGATE can filter out.
    4. ROWS is used here as an expanding range (as can be seen by the incongruent use of $ to lock the top row in the range but not the current row). So when this formula is copied down the range expands.

    Incidentally, the new dynamic array functions (specifically, new function FILTER) in Excel365 make this task so simple, quick and easy that it really is remarkable. Soon enough, Microsoft says everyone in 365 will get the new deal.
    Last edited by DRSteele; Jul 18th, 2019 at 04:11 PM.
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can someone please help me understand a formula from an ExcelisFun video?

    Thank you for the reply and the welcome. This makes so much more sense now! This level of Excel has made me opened my eyes on all the possibilities of the program. I can't wait to learn more about side of things, than the usual Lookup and basic math and logic functions I'm familiar with.

    And RE: the filter in 365, are you saying that I can simply extract data from non-adjacent cells without a need for this long formula?

    Thanks again!

  4. #4
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,987
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Can someone please help me understand a formula from an ExcelisFun video?

    You betcha. Check out how FILTER works.

    https://youtu.be/2kvPdv_nvbM
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •