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

ztt86

New Member
Joined
Jul 18, 2019
Messages
2
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top