Back
Excel

Formula editing improvements Part 3: new functions

In addition to improving the formula editing UI in Excel 12, the team has spent some time adding to Excel’s function library.  Over the years, customers have found new ways to combine and leverage the functions in Excel to build all sorts of things, but there remain many areas where our customers would like to see need new capability.  This release, we have targeted three areas in which to improve our function library – the Analysis ToolPak, SQL Server Analysis Services, and the most common requests we hear from customers.

First, we have fully integrated the Analysis ToolPak functions into the Excel function library, making these functions first-class citizens and eliminating issues relating to the fact that they had been delivered as an add-in in the past.  Users already find a great deal of value in these functions and, from Excel 12 on, they can rely on them to simply work the way the rest of the Excel function library works.  This means users no longer have to run the add-in to use the functions, the functions will show up in Formula Autocomplete (see previous post), the functions will offer the same tooltips as other native Excel functions, etc.  

Second, we have added a new set of functions that allow users to extract information from SQL Server Analysis Services.  For the benefit of readers that are not familiar with SQL Server Analysis Services, let me give you a really high-level overview.  In addition to its relational database product, SQL Server includes a feature named Analysis Services which provides business intelligence and data mining capabilities (for those interested, more information can be found here).  In Excel 12, we have added a set of functions that give users the ability to retrieve SQL Server Analysis Services data directly into cells.  There is a fair bit to cover in this area, so I will write a few posts on these formulas in a few weeks.

Third, we’ve added five commonly requested functions to the Excel function library:

  • IFERROR
  • AVERAGEIF
  • AVERAGEIFS
  • SUMIFS
  • COUNTIFS

Here is more detail on each:


IFERROR(Value, value_if_error)

The most common request we hear in the area of functions is something to simplify error checking.  For example, if a user wants to catch errors in a VLOOKUP and use their own error text opposed to Excel’s error, they have to do something like this using the IF and ISERROR functions:

=IF(ISERROR(VLOOKUP(“Dave”, SalesTable, 3, FALSE)), ” Value not found”, VLOOKUP(“Dave”, SalesTable, 3, FALSE))

As you can see, users need to repeate the VLOOKUP formula twice.  This has a number of problems.  First, it is hard to read and hard to maintain – if you want to change a formula, you have to do it twice.  Second, it can affect performance, because formulas are quite often run twice.  The IFERROR function solves these problems, enabling customers to easily trap and handle formula errors.  Here is an example of how a user could use it in the same situation:

=IFERROR(VLOOKUP(“Bobâ€, SalesTable, 3, false), “Value not foundâ€)

Less to author, less to maintain, faster performance.


AVERAGEIF(Range, Criteria, [Average Range])

Another very common request is for a single function to conditionally average a range of numbers – a complement to SUMIF and COUNTIF.  Accordingly, we have added AVERAGEIF, allowing users to easily average a range based on a specific criteria.  Here is an example that returns the average of B2:B5 where the corresponding value in column A is greater than 250,000:

=AVERAGEIF(A2:A5, “>250000â€, B2:B5)

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])
COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2…])
AVERAGEIFS(average_range, criteria_range1, criteria1 [,criteria_range2, criteria2…])

A third very common question we hear is “how do I sum/count/average a range with multiple criteriaâ€.  For example, if a user had the following range, how could they sum “Value†where Fruit = “Apple†and Value = “Oneâ€. 

There are a number of ways to do this in Excel 2003 – for example, our user could array-enter the following formula:

=SUM(IF(C2:C17=”Apple”, IF(D2:D17=”One”, B2:B17, 0), 0))

But the formula is hard to set up correctly, many users do not know about array formulas, and it is harder to read.

In Excel 12, this task will be much simpler using the SUMIFS formula:

=SUMIFS(B3:B18, C3:C18,”=Apple”, D3:D18, “=One”)

The formula is much simpler to write, easier to read, and doesn’t require array entry. 

COUNTIFS and AVERAGEIFS, also new to Excel 12, work the same way with the same benefits.

That sums up our new functions (more detail to come on the Analysis Server functions later though).  Next up… Formula Editing Part 4: Defined Names.