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

[ztt86]

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!

 

[DRSteele]

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.

 

[ztt86]

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!

 

[DRSteele]

You betcha. Check out how FILTER works.

https://youtu.be/2kvPdv_nvbM