|
Excel has a special kind of advanced formula known as an array. These are different from conventional formulas in that a single array formula can return more than one result. This powerful feature can be very useful, but it needs to be used with skill and care.
In order to create an array formula, you must do the following:
| 1. | Select the range of cells which you wish to contain the array formula; |
| 2. | Type in the formula, but do not press Enter; |
| 3. | Press Ctrl+Shift+Enter (instead of Enter) to turn the formula into an array formula in all of the selected cells. |
To understand this, try the following simple example on a blank worksheet (and see the screenshot on the next page):
| 1. | On a blank worksheet, type decimal figures into cells B4 to G4, making sure that some of the numbers are positive and some are negative; |
| 2. | Select a cell in a different row; |
| 3. | Type in the formula: =min(if(B4:G4<0,0,B4:G4)), but do not press Enter; |
| 4. | Press Ctrl+Shift+Enter to create the array formula. |
Note that the formula is surrounded by braces: { }. This identifies it as an array formula. This particular formula finds the minimum value in the selected range of numbers which is greater than zero. The IF statement returns all of the values in the range which are greater than zero and then the MIN function finds the minimum of those. The key to this formula working as it does is that as an array formula, the IF statement can return more that one value.
When using arrays, there are a few important points to keep in mind.
| • | Excel will not allow you to modify the formula in one of the array formula cells without modifying them all. This is because all of the cells in an array formula must by definition contain exactly the same formula. |
| • | If you select the entire range containing the array formula, you can cut the selection and paste it anywhere else on the worksheet. In other words, the array does not have to be lined up with the cells it references. |
| • | Finally, note that Excel ensures that data tables recalculate by placing them in a special kind of array formula. |
|