etc.—can take the place of potentially thousands of functions,
thereby reducing both recalculation time and workbook
When using Excel's database functions, you can specify up to 256 different criteria. You might, for example, want to sum amounts in column A where the corresponding amount in column B is greater than 100 and the corresponding age in column C is less than 40. If, however, you want to sum amounts where corresponding amounts in column B are less than 50, you need to use another function and a different range of criteria. It would much easier if you had a single function and could easily and quickly change the criteria! If you have never used Excel's database functions before, we strongly recommend that you familiarize yourself with them, as they are very good for extracting statistical information from an Excel database or table.
To see how this works, set up your data as shown in Figure 6-18. Keep the column headings the same, but the
data that resides in it can be any fictitious data. Name this table
of data, including all column headings,
AllData. Name the sheet Data.
Figure 6-18. Proposed data
Insert another worksheet and call this worksheet
Results. In cell A2, enter the following
Copy this across to cell F2 so that you ...