![]() The first is the range A1:B18, containing all of the data including column headings. The DAVERAGE function is in cell D15 and has this formula: =DAVERAGE(A1:B18,B1,D1:D2) The INDEX function returns the third item in the range, the name “East”. The range D7:D10 contains our region names. The INDEX function returns an item from a range. We need the name of the selected region, and to get it we use the formula in cell D5 in Figure 1-8: =INDEX(D7:D10,D4) East is the third item in the list of regions, therefore the Cell link (D4) contains the number three. The Cell link does not get the region name, only a number. When the user selects one of the four regions, the combo box puts a number in the Cell link telling which region was selected. The region names are in the range D7:D10, so that is the Input range. ![]() The Input range tells the combo box what values to display. It takes a little practice to become proficient with ADDRESS and INDIRECT, but it’s worth it because they provide great flexibility in referencing data in Excel. The formula in cell D20 could thus also be written as: =INDIRECT(ADDRESS(D12,D11))Īlthough this may seem a little complex, these functions are very useful when referencing data using both row and column values. In cell D20 the INDIRECT function uses the address built in cell D14 to retrieve the value in cell D3. The address alone does not help much, but the INDIRECT function returns the value corresponding to an address. It lets you include the sheet name and builds it into the returned value. If you need an address to include the sheet name, and you will if you are referencing data on a different sheet, use the version in cell D17. The ADDRESS function in cell D14 uses them to build the address. The row and column numbers are in cells D12 and D11. D3 is the fourth column and the third row. This time the data extends over several columns. Figure 1-4 contains just such an example. It could be anywhere on the worksheet, or even on another worksheet. The INDEX function works in most cases but sometimes you may not know the row or column of the required value. The formula in cell C4 returns a value of Blue as text. The fifth color, the one in cell A5, is Blue. ![]() The number in cell C2 tells the formula which color is required. The first entry in the formula is the range where the names of the colors are. In cell C4, the formula is: =INDEX(A1:A7,C2) Excel then displays the formula in brackets, as shown in cell B17: This is done by pressing Ctrl-Shift-Enter, all at the same time while entering the formula. However, this returns a value error! The error appears because we need to enter the formula in a special way that tells Excel the formula applies to the ranges and not to individual cells. We want the average of the differences, and that is what the formula is asking for. ![]() The formula in B15 is: =AVERAGE(A2:A10-A1:A9) With an array formula we can get the same answer using only one cell. Then we fill this formula down to cell B10. In cell B2 we calculate the difference between A2 and A1. The normal way to approach a problem like this is to add a new column with an intermediate calculation. This situation is illustrated in Figure 1-1. Suppose we have a list of numbers, and we want to know the average amount of change from one number to the next. Excel array formulas give you the ability to work on ranges of cells all at once.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |