Array Formulas Solve Complex Excel Needs
12:06 am August 13, 2008 by Brian J. Ritchey
There are plenty of reporting tools available for law firms. However, one of the common questions firms have when looking at reporting software is “Can you export to Microsoft Excel?” Microsoft Excel has become the tool for manipulating financial data. Therefore, an understanding of some of the more powerful features will help you with the more complex needs, such as (from Microsoft):
- Counting the number of characters in a range of cells.
- Sum numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.
- Sum every nth value in a range of values.
An array formula “is a formula that works with an array, or series, of data values rather than a single data value.” In other words, for example, you can set up a formula to show a result based on a range of results from a column. One way to use this is to measure your AR numbers. Let’s say that you rank AR priority based on age, amount, and payment history. You can create a column representing the average age of AR, one for the amount of AR, and one with a credit rating based on past payment history (using a scale of A-F).
You can then create an array formula in a cell that can produce a result based on your logic for determining AR priority. If you decide that A list clients with AR between $1,000 and $10,000 with an average age under 200 days should be excluded from your collections process, you can create an array formula that can sum all of the clients that fit these variables. There are many uses of array formulas that will help you track important metrics. They can be used to track fee earner capacity, mark-down frequency, and really anything you want to measure that is being tracked by the firm.
There are several websites that help in creating array formulas, but I really like the one set up by Pearson Software Consulting. Instructions on creating an array formula from the Pearson site:
To enter a formula as an array formula, type the formula in the cell and press the CTRL SHIFT and ENTER keys at the same time rather then just ENTER. You must do this the first time you enter the formula and whenever you edit the formula later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces — Excel will display them automatically. If you neglect to enter the formula with CTRL SHIFT ENTER, the formula may return a #VALUE error or return an incorrect result.
To learn more about array formulas, visit their site by clicking here.
Comments
Feel free to leave a comment...