Explanation of a formula

[Richards440]

Hi All,

I am currently working on a large data set. I took the 10 largest values within this data set using the large formula. After this I wanted the area these values came from and found this formula online, =INDIRECT(ADDRESS(1,SUMPRODUCT((B1:M9=A13)*COLUMN(B1:M1)))). It seemed to work but can anybody explain to me why this worked?

B1:M9 = Data
A13= Value to look for
B1:M1 = Naming convention

 

[Toadstool]

Hi Richards440,

I'm not exactly sure what you want the formula to do so let me restate your question.

In cells A13 to A22 you have the top ten largest value from your matrix and you want to return some kind of title from cells B1 to M1 to show in which column they appear?

If so then I'll start with a couple of changes. If B1 to M1 are the title/headings to display then they probably shouldn't be in the search matrix and I believe the only relative address used should be A13 so you can copy down the formula. I've therefore modified the formula to INDIRECT(ADDRESS(1,SUMPRODUCT(($B$2:$M$9=A13)*COLUMN($B$1:$M$1))))

My explanation would be that the SUMPRODUCT produces an array of TRUE and FALSE results (1 and 0) looking for the A13 value in the matrix. As you have 12 columns (B through M) then you'll get 12 TRUE,FALSE values for row 2 then 12 TRUE,FALSE values for row 3, etc. and as we're looking in a matrix B to M (12 columns) and row 2 to 9 (8 rows) then we'll see 96 TRUE or FALSE results.
Now it multiplies those results by the column number for B to M (2 to 13) so as we're performing arithmetic the TRUE becomes 1 and the FALSE becomes 0. Let's assume the number we're looking for can be found in cell D3, then we'll get for row 2 the 12 FALSE (zero) values multiplied by the COLUMN number 2 through 12 which all result in zero. Now for row 3 we'll get two more FALSE values multiplied by the COLUMN numbers 2 and 3 but then we'll hit the TRUE match in D3. The TRUE, or 1, multiplied by the COLUMN 4 will return a 4 in that SUMPRODUCT array which is otherwise zeros. So SUMPRODUCT * COLUMN has given us the column number of the match.

ADDRESS default gives us a cell address in the format $Z$22 for a specified row and column number. The 1 is hard coded as row 1 and we've just returned a 4 from the SUMPRODUCT * COLUMN so the ADDRESS in this example will return $D$1

INDIRECT default lets you specify a cell address, or range of cells, as a text field, e.g. "$Z$22". While INDIRECT can get quite complex when used with other functions in this case it is quite simple, it will return the contents of the cell from the ADDRESS function, $D$1. If we've labelled the row 1 title headings in B1 to M1 with "Batch 39" through "Batch 50" then INDIRECT("$D$1") would return the contents of D1, "Batch 41".

Here's that explanation as a worked example.
HINTS:

1. Using Formulas, Evaluate Formula will let you step through the calculation (though you may want to use a smaller matrix to make it easier to follow).
2. Normally you want to avoid volatile functions (ones which recalculate with any sheet change) especially if you have a large dataset. INDIRECT is volatile and in this example INDEX would give the same result, changing our formula to INDEX($B$1:$M$1,,SUMPRODUCT(($B$2:$M$9=A13)*COLUMN($B$1:$M$1))-COLUMN($A$1))
3. The formula will only work if the there are no duplicates in the Top 10 list, e.g. if the number sought is found in columns 9 and 8 then the formula would return 17 so point the INDIRECT at $Q$1. If you'd like the change to fix this then let me know. Hint in a Hint: We'll use another LARGE and a COUNTIF.

Richards440.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Batch 39	Batch 40	Batch 41	Batch 42	Batch 43	Batch 44	Batch 45	Batch 46	Batch 47	Batch 48	Batch 49	Batch 50
2		137	157	1100	1439	1874	1118	1344	1533	227	1722	1660	1789
3		1806	121	2011	1026	547	409	1791	215	1513	208	1730	1275
4		1863	1496	265	3	674	1069	1042	719	869	1292	1250	1110
5		443	1043	1280	1615	887	1569	605	1294	637	1844	164	726
6		28	1683	231	496	660	301	1665	1529	925	1209	230	1298
7		605	538	1129	1675	337	1549	1503	1360	1848	218	1186	321
8		68	318	1210	1615	1961	1702	193	686	1987	159	269	1151
9		868	1140	413	1788	1787	1178	1383	1598	1511	837	1352	334
10
11
12	Top 10	Heading
13	2011	Batch 41
14	1987	Batch 47
15	1961	Batch 43
16	1874	Batch 43
17	1863	Batch 39
18	1848	Batch 47
19	1844	Batch 48
20	1806	Batch 39
21	1791	Batch 45
22	1789	Batch 50
Example1

Cell Formulas
Range	Formula
A13:A22	A13	=LARGE($B$2:$M$9,ROW()-12)
B13:B22	B13	=INDIRECT(ADDRESS(1,SUMPRODUCT(($B$2:$M$9=A13)*COLUMN($B$1:$M$1))))