Array Variables in Excel Formulas

[brharrii]

Hi All,

I recently discovered a way to use the aggregate function in conjunction with index / match to identify min / max values for like records in a table (i.e. if I have a table of animals and their weights, I could use this function to identify the smallest dog, or the largest cat, etc.).

This is what the formula looks like:

=AGGREGATE(15, 6, B4:INDEX(B:B, MATCH(1E+99, B:B))/((A4:INDEX(A:A, MATCH(1E+99,B:B))=G1)),1)

Where column A is my criteria column (dog, cat, etc.) and column B is my values column (e.g. height).

The formula works great, I've already implemented it a few times with success. But it bothers me that I don't understand how a certain part of it works. There are two places in the formula where it appears to declare array variables (B4:Index(....) & A4:Index(....)). I've tried to find documentation on how this works, but haven't had any luck yet.

Any ideas?

Thank you,

Bruce


PS - This is where I discovered the formula in the first place:

https://stackoverflow.com/questions/36953520/finding-minimum-value-in-indexmatch-array-excel

 

[steve the fish]

The easiest way to see what is happening is to use the evaluate formula tool on the formulas tab. Click your formula cell and step through the evaluate formula button. The index will just be creating a range of cells so lets say the last numeric cell in column B is cell B50 it is just creating the range B4:B50 for the aggregate function to use.

 

[brharrii]

The easiest way to see what is happening is to use the evaluate formula tool on the formulas tab. Click your formula cell and step through the evaluate formula button. The index will just be creating a range of cells so lets say the last numeric cell in column B is cell B50 it is just creating the range B4:B50 for the aggregate function to use.

Thanks Steve,

I've used the evaluate formula feature, what I was hoping for was some documentation on how this actually works, what constraints it has, etc. so that I can add it to my tool box for future use. I think my struggle is just not knowing what to search for. When I search arrays formulas in excel I get a lot of stuff on ctrl + shft + enter formulas {}, but nothing about declaring a whole column as a variable array.

 

[steve the fish]

You arent using the whole column as such. You are in the match but the match then returns a number. This number is used by index to return a reference. This reference coupled with the B4 is what is used by the aggregate.

 

[brharrii]

You arent using the whole column as such. You are in the match but the match then returns a number. This number is used by index to return a reference. This reference coupled with the B4 is what is used by the aggregate.

oh wow, I completely misread that. I see, that's just a sneaky way of finding the range.

Nice!

I just simplified the formula by replacing that all with column names like so:

=AGGREGATE(15, 6, (Table1[Number])/(Table1[Name]=G1),1)

Seems to still do what I want it to!

Thank you!