Array Formulas in Excel

 613-749-4695 [email protected]

 Excel Chapter 23 of 24: Array Formulas in Excel For years I have been promoting the use of ARRAY FORMULAS (SUM) in Excel spreadsheets as the magic formula that solved all the problems when organizing data into reports. When I discovered the SUMPRODUCT formula, I completely abandoned the ARRAY FORMULAS. The SUMPRODUCT formula does the same thing as the ARRAY FORMULA (SUM) but is more accessible to all and much easier to use. Array formula have been created when computers were much less powerful and memories much smaller. Computers have evolved so much that array formulas are not really useful anymore. Some of them though are indispensable. Here are two examples of things you cannot do without using array formulas. Before getting into array formulas  be sure that you have discovered the extremely powerful SUMPRODUCT (Chapter 13) and INDEX/MATCH (Chapter 14). MAX To enter an array formula in a cell you don't simply use the "Enter" key but you hold the SHIFT and CTRL keys while you click on ENTER. If you do it right you will see special parentheses appear at each end of the formula { }. Among the functions that are used in array format are MAX and MIN. For example: In the table above when is the last  date at which John Spencer reported? 5/11/2006 The array formula for that is:{=MAX((A2:A14="John" )*(B2:B14="Spencer" )*(C2:C14))} What is the maximum weight that John Spencer has reached? 162{=MAX((A2:A14="John" )*(B2:B14="Spencer" )*(D2:D14))} INDEX/MATCH INDEX/MATCH formulas by themselves are very powerful and less limited much easier to handle that VLOOKUP. But there are some things that even INDEX/MATCH cannot do. The other Excel array formula that I use is INDEX/MATCH when I want to lookup a table based on criteria in more than one column.