Array Formulas in Excel

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

 

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:

Excel Array Formulas

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.

 

Next Chapter: All the Functions in Excel

Excel Table of Contents
or use the quick links below

 

Discover Even More in 50 Excel spreadsheets

 

    

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Array Formulas in Excel