Welcome to MSDN Blogs Sign in | Join | Help

Microsoft Excel

The team blog for Microsoft Excel and Excel Services.
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.


 

Posted: Thursday, October 20, 2005 2:28 PM by David Gainer

Comments

XL-Dennis said:

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
# October 20, 2005 7:11 PM

Rob van Gelder said:

David,

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.
# October 20, 2005 8:10 PM

mschaef said:

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.)
# October 20, 2005 8:31 PM

David Gainer said:

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.
# October 20, 2005 8:32 PM

Harlan Grove said:

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.
# October 20, 2005 8:40 PM

Biff said:

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 ?
# October 20, 2005 9:59 PM

Harlan Grove said:

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.
# October 20, 2005 10:14 PM

Harlan Grove said:

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}.
# October 20, 2005 10:28 PM

Harlan Grove said:

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?!
# October 20, 2005 10:37 PM

simon murphy said:

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 <watch out for an announcement about 2006!> - 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
# October 20, 2005 10:51 PM

JC said:

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
# October 21, 2005 3:35 AM

Dave Cope said:

David,
While I understand that:

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

... 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!

# October 21, 2005 5:47 AM

Marcos Martins said:

Just 5 new functions?
# October 21, 2005 8:46 AM

Tianwei said:

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...
# October 21, 2005 9:22 AM

Harlan Grove said:

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?
# October 21, 2005 12:22 PM

David Gainer said:

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.
# October 21, 2005 7:36 PM

Harlan Grove said:

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?!
# October 21, 2005 8:12 PM

Jean Martineau said:

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
# October 21, 2005 11:49 PM

simon murphy said:

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
# October 23, 2005 4:56 PM

Harlan Grove said:

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.
# October 24, 2005 12:16 PM

David Gainer said:

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.
# October 24, 2005 8:45 PM

Steve said:

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( <value>, <condition>, <value to return if false> )

Where the condition is specified by a string in the same way as the SUMIF function, and <value> is returned if the condition is met.

Looking forwards to hearing about the new table functions too...
# October 26, 2005 9:07 AM

Harlan Grove said:

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)<0 returns TRUE
=ABS(QUOTIENT(3,-5)=0 returns TRUE

Note that this is decidedly out of compliance with IEEE 754, which does allow negative zero, but requires that it be treated as equal to (positive) zero.
# October 31, 2005 1:25 PM

excel formula said:

PingBack from http://ibrahim.freedownloadreport.info/excelformula.html

# July 31, 2008 2:03 PM

Display Month - 1 | keyongtech said:

PingBack from http://www.keyongtech.com/988923-display-month-1-a

# January 21, 2009 9:53 PM
New Comments to this post are disabled
Page view tracker