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.
Tips
How-to
News
Videos
Stories
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
Greetings
Let me start with a couple of broad comments. First, we added 7 Analysis Services functions, 5 ‘general’ functions, and 93 ATP functions (understood many will not see these as new since they were already available with the product). We did not change the behaviour of any other functions (so Harlan to answer one of your questions MOD will be the same in Excel 12). In general, the area of functions in Excel is a very rich area, and we know lots of users have a lot of great ideas or requests. With respect to Excel 12, our goals were to integrate the ATP (huge customer request), provide a good set of functions for working with Analysis Services, and address the top customer general requests. There is definitely a lot more we can this area over time, and it is an area where I am interested in requests and scenarios for consideration in future versions of Excel. Feel free to post comments or use the send email button.
Biff – IFERROR does not have a default value for value_if_error; the user must specify the value to return.
Harlan – As above, we have not changed the SUMIF behaviour you mentioned. RANDBETWEEN has been updated to share the logic in RAND. Also, yes, this formula =IFERROR(MATCH({1,2,3},{1;3;5;7},0),{"a";"b";"c"}) returns the array you mention above.
Simon – Thanks for the feedback. See Harlan’s last post for a way to trap specific errors.
JC – We have made improvements that I think you will find helpful. I will be getting to them in this blog, but probably not for a few more months.
Dave – Thanks for the kind worlds. Let me try and be more clear. Using Excel 2003, the usual approach to checking for errors is like this: =IF(ISERROR(VLOOKUP("Dave", SalesTable, 3, FALSE)), " Value not found", VLOOKUP("Dave", SalesTable, 3, FALSE)). Excel runs the VLOOKUP function once, and if it doesn’t produce an error, it then runs it again, meaning the same function gets run twice. If you have a lot of these, your sheet can slow down quite a bit. With the new function, VLOOKUP is only ever run once, so your sheet should get faster.
Tianwei – We have done some work in the area of trust that should address your concerns around macro warning dialogs. Once I get to writing about it, I look forward to your feedback on whether it also addresses your other request below.
In re MOD, you're not likely to get many customer requests because most Excel users probably don't use it (or 3/4 of the other functions in Excel, e.g., LINEST and LOGEST, which Microsoft overhauled in XL11).
However, it's basic math.
All other spreadsheets extant that run on hardware that supports IEEE 754 floating point math provide MOD functions or equivalents that provide full IEEE 754 functionality. As I also pointed out, even formulas in Word tables seem to provide it. Why is Excel a crippled exception?
Anyone other than a Microsoft employee would call this a bug. Is there any chance this 'feature' will be changed in the first service pack for Excel 12? It's not like Microsoft hasn't changed the semantics of built-in functions before: the semantics for blank cells in X and Y range arguments to the SLOPE, INTERCEPT and FORECAST functions changed in XL11 vs previous versions. Fixing the MOD function would even be fully backward compatibility: arguement pairs that give numbers rather than errors in prior versions would give the same results in XL12, and argument pairs that give numbers only in XL12 would still give #NUM! errors in prior versions. Somehow, y'all were able to stomach fixing LINEST/LOGEST so that highly colinear dependent variable arguments give numeric results in XL11 even though they give error results in prior versions.
Why don't MOD get no respect?!
IFERROR will be faster to use than the traditionnal way. Thanks for the improvement.
Why not a HEADER FORMULA. When I bring data through queries, I typically end up adding calculated columns on the excel side. Each column has the same fomula, I just copy the formula up to the last record. At every refresh, the number of record may change and I could need to copy further down. When I am tired to repeat the process, I copy the formula for more rows than enough and typically, I end up having unecessary calculated rows. Usually, I keep a copy of the formula over the header line. In this case, since we can treat the data as a "table", we could prevent this copy process by defining the calculated columns from the top line, the HEADER FORMULA. The calculted rows would adjust automatically the same way the filters does.
Jean
David (and Harlan)
Sorry I mustn't have been clear in my post.
What I meant to say was:
'IF(ISERROR(' is a dangerous construct because it can hide unexpected errors.
The replacement IFERROR( you are incorporating into Excel 12 is also dangerous for the same reasons.
I think a more fine grained version would be safer.
Harlan has given an example of how to achieve what I am on about currently. What I was trying to suggest is that you create something like that with slightly less tortuous syntax, rather than the error prone IF(ISERROR replacement you are proposing.
Harlan, what I am suggesting is they make the functionality you demonstrate (neat function btw) a bit easier to work with, and yes one way would be a third parameter to the new IFERROR. Do you think that would be a good move?
cheers
Simon
To simon murphy,
Yes, it'd be nice if the new IFERROR function took an optional 3rd argument that would be interpreted as an array (like the 2nd argument to FREQUENCY) of the error values to trap.
Now we get to see whether the feature lock will trump sensible suggestions.
Jean - see upcoming table posts. I think you will get what you want in Excel 12. Please let me know.
Simon, Harlan, thanks for the feedback.
The IFERROR function is an excellent addition, but please don't stop there - can we also have IFNUMBER, IFBLANK, IFNA etc.? How about a more generic if function:
IF2( , , )
Where the condition is specified by a string in the same way as the SUMIF function, and is returned if the condition is met.
Looking forwards to hearing about the new table functions too...
Since the old ATP functions are going to become built-in functions, that presumably includes the QUOTIENT function. Will the new, built-in QUOTIENT still produce negative zeros? E.g.,
=QUOTIENT(3,-5) returns -0
Note that this leads to the following silliness.
=QUOTIENT(3,-5)=0 returns FALSE
=QUOTIENT(3,-5)
Comments: (loading) Collapse