Formula editing improvements Part 3: new functions

re: Formula editing improvements Part 3: new functions

Harlan Grove — Mon, 31 Oct 2005 18:25:44 GMT

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)

re: Formula editing improvements Part 3: new functions

Steve — Wed, 26 Oct 2005 14:07:20 GMT

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

re: Formula editing improvements Part 3: new functions

David Gainer — Tue, 25 Oct 2005 01:45:36 GMT

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.

re: Formula editing improvements Part 3: new functions

Harlan Grove — Mon, 24 Oct 2005 17:16:54 GMT

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.

re: Formula editing improvements Part 3: new functions

simon murphy — Sun, 23 Oct 2005 21:56:53 GMT

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

re: Formula editing improvements Part 3: new functions

Jean Martineau — Sat, 22 Oct 2005 04:49:36 GMT

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

re: Formula editing improvements Part 3: new functions

Harlan Grove — Sat, 22 Oct 2005 01:12:11 GMT

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

re: Formula editing improvements Part 3: new functions

David Gainer — Sat, 22 Oct 2005 00:36:23 GMT

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.

re: Formula editing improvements Part 3: new functions

Harlan Grove — Fri, 21 Oct 2005 17:22:45 GMT

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?

re: Formula editing improvements Part 3: new functions

Tianwei — Fri, 21 Oct 2005 14:22:56 GMT

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