You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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:
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.
Comments: (23) Collapse
David,
All the presented functions are welcome :)
Can we assume that the new native AT-functions will be working in the same way as the present built-in functions do when using different language versions?
Have You considered to improve the lookup functions, i e the possibility to use several lookup values?
Kind regards,
Dennis
additions to the Excel function set. I'm sure many users will see this as a big step forward.
Of course, SUMPRODUCT has been able to do the SUMIFS equiv (without an array-enter) for some time:
=SUMPRODUCT((C2:C17="Apple") * (D2:D17="One"), B2:B17)
One really annoying thing was that you were unable to use an entire column.
To questions:
Will SUMIFS (and others) be able to select an entire column? I'm assuming yes, since SUMIF is currently able to.
Will SUMPRODUCT be able to select an entire column? eg.
=SUMPRODUCT((C:C="Apple") * (D:D="One"), B:B)
Again, nice work - congrats to the Excel team.
Looks very nice.
"Second, it can affect performance, because formulas are quite often run twice. "
You know, there's a decent optimization for this: during worksheet evaluation, look for function calls with the same arguments and cache the result. (Memoize (not a typo), otherwise.)
Hi Dave - Yes - it is one of the advantages of making the ATP functions built in. In Excel 12, Analysis ToolPak functions will automatically translate just like all the other built-in functions.
Hi Rob - SUMIFS will be able to select an entire column. We have not made any changes to SUMPRODUCT.
I have to ask: is the reason DSUM, DCOUNT and the other database functions remain as useless as they are because IBM, which acquired Lotus Development corp, acquired LDC's DataLens patents, and 123's @DSUM, @DCOUNT etc. criteria expressions as third arguments are covered under those patents?
In 123, if the range includes field names, as in your example, the equivalent of your SUMIFS formula is
@DSUM(B2:B18,"Value",FRUIT="Apple"#AND#NUMBER="One")
Granted this depends on the presence of field names in the top row of the range, but it's more flexible than your SUMIFS approach. How so? It accepts all the boolean operators, #OR# and #NOT# in addition to #AND#, and it supports parenthesized subexpressions, e.g.,
@DSUM(TBL,"X",(FOO="a"#AND#BAR="z")#OR#Y>100)
SUMIFS, COUNTIFS and AVERAGEIFS only support AND.
Also, if you had upgraded the list processing functions, then that would have included DMAX, DMIN and DGET. And an upgraded DGET would have gone some way to addressing the relatively frequent requests for some means of lookups on multiple fields.
Will SUMIF, SUMIFS etc. finally support multiple area ranges? 3D references? Arrays?
As for the ATP functions, does this mean you've finally dumped the horrible random number generator in the ATP in favor of the built-in RAND function for RANDBETWEEN and the random number generator in the Data Analysis ToolPak? Is the Data Analysis ToolPak now built-in?
As for building the ATP functions into Excel, that's a good thing for the more general functions (e.g., EDATE, NETWORKDAYS, RANDBETWEEN), but I have to wonder how may users demanded the Bessel and complex arithmetic functions be promoted to first class functions.
Now we'll have BESSELJ and IMABS built-in. Pity we'll still not have any built-in means of searching strings right to left for the last instance of a given substring, so we'll still get the chance to use long & error-prone array formulas like
=MATCH(2,1/(MID(s,ROW(INDIRECT("1:1024")),LEN(ss))=ss))
or slow udf wrappers around VBA's InStrRev. Or other common requests in the newsgroups, like the ability to find the 2nd or subsequent match in an array or lookup.
Just 5 new functions?
Well, those are 5 good ones !
How easy would it be to add a new function:
=CONCATENATE(A1:A10,",")
OR:
=CONCATENATEIF(A1:A10,B1:B10="Biff",",")
Of course they would need to ignore empty cells!
I'm sure most would agree that:
=A1&","&A2&","&A3&","&A4&","&A5&"," etc., etc.
is kind of "neanderthal" !
Will the IFERROR have a default value_if_error ?
Biff...
|Just 5 new functions?
...
It took them 4 versions to get around to adding any new functions (IIRC and the last time they added new functions it was GETPIVOTDATA in XL8(97)), so why be greedy?
|Will the IFERROR have a default |value_if_error ?
Since IFERROR already exists, and it looks like Microsoft is only adding an optional second argument, IMO it damn well better work as it does currently when called with only one argument - return TRUE if the 1st argument evaluates to an error, FALSE otherwise.
Sorry, Biff. I finally realized the new function is named IFERROR rather than ISERROR.
The 2nd argument doesn't appear in brackets, so it looks like it's a mandatory argument. However, that begs the question whether it'll have nice array semantics, e.g., whether
=IFERROR(MATCH({1,2,3},x,0),{"a";b";c"})
will return the same 2D array as
=IF(ISERROR(MATCH({1,2,3},{1;3;5;7},0)),{"a";"b";"C"},MATCH({1,2,3},{1;3;5;7},0))
namely, {1,"a",2;1,"b",2;1,"c",2}.
I've asked this before, though it might have appeared rhetorical. I'll ask it for REAL this time.
Will XL12 finally fix the MOD function? In the sense that MOD(2^30,3) would return 1 and MOD(2^31,3) would return 2 rather than both returning #NUM!, which is what both calls return in previous versions of XL.
Just to put this in perspective, the formulas =MOD(2^30,3) and =MOD(2^31,3) return 1 and 2, respectively, as formulas in Word tables. What does the Word development team know about basic mathematical programming that the Excel team doesn't?!
David
Glad to hear there are some new formulas.
Would it be possible to make your new iferror more focussed than just capturing any error?
Ideally I would like to be able to specify whether to catch #n/a or #ref or #div/0 etc and give a meaningful relevant warning.
The current IF(ISERROR( approach is too broad brush and can hide real errors that need dealing with - eg =IF(ISERROR(#REF!),0,#REF!) if someone deleted the cell(s) the formula referred to the error is hidden (cell shows 0). IF(ISNA( would often be a better choice.
I mentioned this specific issue at the Excel User Conference this year - you should only catch the exceptions you expect, otherwise you may be hiding unexpected errors - like a general catch statement in .net/Java/C++ etc.
Cheers
Simon
I work for a Litigation Support Vendor and we routinely receive Excel documents for printing or converting to an electronic page-based format (tiff image or pdf primarily). Lawyers usually want these files printed to look beautiful, and as you may know, by default, real world use of Excel isn't made to print on 8.5x11 paper. So we have staff that manually formats XLS documents. This takes a lot of time, because we will get hundreds of documents each with many sheets. While we use Excel for other aspects of our jobs (we are really looking forward to 1M rows!) we despise Excel for when you want to print.
I don't know if you're considering user wish list items, but some kind of Print-Format feature that wouldn't cut things off, and keep them above ~60% for readability would be quite amazing. At the very least we would like to see some sort of semi-visible page outline and maybe users would have a better idea on how to structure their documents for easy printing.
Thanks in advance!
Jason
While I understand that:
... will provide an alternative error message, I can't see how it will reduce multiple VLOOKUPs - one to test for an error, another to return a value.
Or, are you saying that, with IFERROR, the result of the VLOOKUP will automatically be returned if there is no error?
Whatever, Excel 12 looks great!
I'll take whatever new functions added! Thanks!
I know you can't possibly add all the user defined functions and super experts like John Walkenbach can still come up with more useful functions for his add-in products. My true hope is Excel can enable true user specific trust models (as I mentioned before) so the subscribers of a "packaged" file can enjoy the macros without being hesitant about the macro warning pop-ups. I also hope a "packaged" file can enable add-in functions for this file only. For example, J-Walk's pup tool has a great text function of ISLIKE. When you use it so much and forget it's actually a third-party function and build in your application. It will blow up at an end-user's desktop. While this makes strong case for everybody to buy the add-in, I'd really like by somehow signing and packaging the application, subscribers can also enjoy whatever add-in functions used in this file.
Anyway, just a wish...
In re simon murphy's comments about trapping only certain errors, it can already be done.
=IF(COUNT(1/(ERROR.TYPE(A1)={3,7})),"trapped",A1)
traps #VALUE! (3) and #N/A (7) errors while passing other error and nonerror values. You're suggesting that Microsft add a 3rd, optional argument containing an array of the error values to trap or to pass?
Comments: (loading) Collapse