Excel 2007 Update: Improvements to the edit Formula function part 3_ new function

In addition to improving the interface for editing formulas, we've spent some time on the built-in Library of Excel 12. Over the years, users have mined many new ways to integrate and play the functions of Excel functions to create a wide variety of formulas. Even so, users expect to have more new functions. In this release, we have targeted three areas to augment the Excel built-in function libraries--The Analysis ToolPak, SQL Server Analytics Services, and the most representative requirements presented by users.

First, we integrate the Analysis ToolPak function into the Excel built-in function library to make it a "first-class citizen" and cancel the original add-in. These functions are of great value to the user. From Excel 12, they can be used as easily as other Excel built-in functions. This means that users do not need to add macros to use these functions directly. They appear in the Drop-down list provided by the Formula AutoComplete feature (see previous posts) and have the appropriate feature hints as well as other built-in functions.

Second, we've also added a set of functions that allow users to get data from SQL Server Analysis Services. Given that some readers are not familiar with SQL Server analysis Services, let me briefly summarize them. In addition to the related database products, SQL Server includes a feature called Analysis Services that provides business intelligence and data mining capabilities (where interested readers can find more information). In Excel 12, these new functions can get data directly from SQL Server analysis Services and stored in cells. I'll write some more posts about these functions over the next few weeks because of the breadth of the coverage.

Third, we've added 5 functions that are representative of the user's requirements, as built-in functions for Excel:

Iferror

AverageIf

AverageIfs

Sumifs

Countifs

Here is an introduction to these 5 functions:

The most pervasive requirement we receive about functions is the process by which users require the simplification of error values. For example, if you want to intercept the error values in the VLOOKUP calculation and replace them with user-defined error prompts, you will have to use a combination of functions like if and iserror.

As you can see, the VLOOKUP formula is used twice here. There are a few problems with this. First of all, maintenance formula is more troublesome, if you want to change the formula, you have to modify two places. Second, it affects the speed of the operation, the formula will often be more than one operation. The Iferror function solves these problems by allowing the user to easily intercept and process the error values calculated by the formula. In the following example, we use the IFERROR function to handle the above scenario:

=iferror (VLOOKUP ("Bob", SalesTable, 3, False), "Value not Found")

Compared with the previous formula, the formula has less writing, less maintenance and faster operation.

Another common concern for users is the need to have a conditional average function as a supplement to SUMIF and Countif. Accordingly, we have added a AverageIf function to average the data that satisfies a certain condition within a certain range. For example, in a range of b2:b5 cells, you would mean an average condition, if the value of the corresponding cell in column A is greater than 250000.