Yes please.
WTF? That formula is the same as the last one, only it uses Table notation!
–Edit–
Out of interest, here’s how that 1st message looks in Excel 2010. (Apologies for the old-school look of these next two screenshots, vs the previous ones. I’m too tight to install Excel 2010 AND 2013 on all the machines in our house, so this screenshot comes from my wife’s PC, which runs XP, on account of that same monetary tightness.)
Let’s say I do decide to take the option offered in that 2nd bullet point – to close the message and correct the formula myself – and click NO. What do I get?
You told me that one click ago. Get out of my way, so I can do what I said I was going to do one click ago…i.e. fix the damn thing!
I spend heaps and heaps of my time on usability things when I build stuff in Excel. I can’t comprehend why these really crappy legacy usability issues are still perpetuated by the MS developers release after release. WTF.
Being an Excel nerd, I had gotten quite used to seeing the ‘WTF’ version over the years !
This post made me laugh out loud ! :D
Btw, Jeff, is the first error message for real ? In which version of Excel ?
2013. MS have got a bit more polite. Unless you are using Tables. In which case they can’t even string together a grammatically correct sentence, let alone be polite.
My point is that it’s exactly the same formula. One uses the old formula notation. One uses the NEW table notation, and that programmer really screwed up. Who the hell writes something in a zillion dollar product that says “Your formuala is missing a parenthesis- -) or(.
Excuse me, Mr MS Programmer, but what the heck does that double dash mean? And do you know how weird that – -) or (. bit looks, wedged on the end of that first sentence. And why the heck don’t you just close the parenthesis for me, like you used to?
To be clear, with formulas, you get that 2nd error message too…but only if you click NO to the first error message. But with tables, you simply don’t see that first error message, it goes straight into the 2nd message.
And I can’t fathom why Excel will automatically add the closing parenthesis to this:
=SUM(A3:A7
…but not to this:
=1+SUM(A3:A7
Furthermore, if I click NO to the first one – because the offered solution after all may not be what I want – why bother me with the 2nd one? How many billion clicks is Microsoft responsible for each year, because of crap usability on even the most basic part of their product?
This kinda bugged me too for some time.
Furthermore I always click NO, because I like to use space characters between operators in long formulas, and for some reason Excel discards all of them when you click YES. I have no clue why my space characters are discarded, since I did put them there on purpose…
Jeff, you seem to have gotten up on the wrong side of your bed today :P
JLeno, I get bugged too when all my carefully inserted spaces or ALT+ENTERs are removed if I click on YES by mistake !
I dunno, no matter what I try I cannot reproduce this behaviour. It completes it nicely, end of story.
Has anyone been successful in being able to communicate these kinds of bugs/problems to MSFT tech support ?
I, for one, have not.
We regularly and mostly bluntly tell them what we don’t like about Excel (don’t worry, we also tell them what we do like), so yes, they do get informed.
In fact, I’m off to point them to this blog post…
It would be great if the auto-fill added the closing parenthesis automatically. For example, if you type “=SUM” then hit the tab key, the auto-fill gives you “=SUM(|”. I put the pipe symbol in there to represent the cursor position. The problem could be solved if that auto-fill closed the function with a parenthesis and still put the cursor in the middle of the parenthesis “=SUM(|)”.
I’m probably stating the obvious here, but it might be a good way to fix it instead of messing with the error message triggers.
The closing parenthesis would only be auto-filled when the remainder of the formula is blank. For example, if you are modifying an existing formula to wrap a VLOOKUP with an IFERROR, then you would NOT want this to happen when you typed “IFE” before the VLOOKUP and hit the tab key: “=IFERROR()VLOOKUP(args)”
Instead, you would want the existing functionality where only the open parenthesis is auto-filled, then you would have to decide where to close the function.
But for the majority of the time when you are writing formulas from scratch, it would be great if the function closed themselves and left the cursor in the middle for you to input the arguments.
Auto closing the function might also work when the next character in the formula is a close parenthesis “)” or even a comma. Since you would likely be adding a function inside an existing argument. It would be more of a closed nesting instead of wrapping a pre-existing formula/function. I have to give that one more thought, but I think auto-closing the function in a few specific scenarios would be great.
And thanks for the good laugh Jeff! :)
Bob, what formula are you typing in?
Note that Excel automatically parenthesizes something this:
=SUM(A3:A7
…but not this:
=1+SUM(A3:A7
I’m probably stating the obvious here, but it might be a good way to fix it instead of messing with the error message triggers.
@Jon: Yeah, that’s half my point. But those error messages do need to be rethought. Apart from the fact that they are crappily written, that 2nd one is completely redundant if you have already pushed no. Can you imagine the carbon emissions caused by the energy requirements of a couple of billion needless clicks on that message? This has got to stop…I live on the waterfront. :-)
Jan Karel: This really basic stuff annoys me so much that I added another tag to the blog called ‘Excel’s UI‘ so that anyone in charge who cares can easily pull up the series. Assuming anyone cares.
Excel is such a cool product, but these little things show a pretty astounding lack of attention to detail in a very key area: Useability.
I know Jon Peltier has plenty of posts about the backwards steps in useability due to how the ribbon is implemented, and due to tweaks between versions. But I’m pretty sure that MS avoid his site like the plague, because Jon’s excellent critique would require them to make major changes to their current approach. So I thought that we might be able to get them to tackle some very low hanging fruit first, to get a taste of how easy it could be to then tackle some of the higher.
Some of the worst useability culprits require only very minor changes. Plus of course an understanding that something is woefully suboptimal. Plus a desire to make the change. Plus the political capital required to get it through the committee. Oh dear…
Who is the Head of Useability, Excel? Is there such a person? Do they perhaps need a guest pair of eyes? I’ve got a reasonable hourly rate and am willing to work remotely or to commute. (Anyone who lives in Middle Earth who looks like they could pass for an elf or an orc in the next installment of The Hobbit is allowed to borrow Peter Jackson’s private jet. People who look like Hobbits, no I’m afraid…there’s a sign painted on the side of the jet by the door saying “You must be this tall to take this ride”)
Are we complaining because I’d like to get in on this. Let’s say I have a table with Quantity, Price, and Tax. I create a formula
=SUM(tblData[@[Price]:[Tax]]). Nice. But I want to add Quantity. So I edit the formula, put the cursor between the equal sign and SUM and type[@Q{tab}. That leaves me with=[@Quantity(tblData[@[Price]:[Tax]]).I have two problems. First, hitting tab inserts the field name but overwrites my SUM function. The other annoyance is that when I type
[@Q{tab}, I expect to get[@Quanity], but I don't get the closing bracket. Is there ever a time I don't want the closing bracket? I don't think so.Dick, that will learn you for being too keyboard-centric! Type [ then swallow your pride, and use the mouse to select Quantity from the dropdown. But I hear ya, my musophobic friend.
Yeah, that lack of closing bracket rips my shorts, too.
I own a copy of an O’Reilly book entitled “Excel Annoyances”. Maybe it`s time it was updated for 2010/2013.
Grufff999: I’m betting that book is already as fat as Professional Excel Development, 2nd ed. New annoyances would make it morbidly obese. :-)
@DK
The Trick is to Type [@], then the Left arrow and type Q and then Tab to complete Quantity (assuming you have only 1 column with Q) :-)
They fixed this problem in the Measures Window of Power Pivot, so if you say =Calculate(Sa and Tab it will complete Sales with a Close Square bracket
It should just take 3 or 4 more versions before they fix it in Excel’s Table References :-)
@sam
I had to type
[ @ ] {F2} {LEFT} Q or it would be in point mode instead of edit mode.Dick: is there any way you can add a ‘subscribe to comments’ email alert to your blog, like Peltier does? (If Peltier has it, then it’s worth having. Unless it’s a beard) I know there’s an RSS feed for comments, but an email would be handy too.
http://www.dailydoseofexcel.com/comments/feed/
There are some plugins to subscribe to comments by email, but they don’t let authors subscribe. WordPress will email you whenever someone comments on your post, but I can’t turn it on for you and off for me (and we both know who wins that one). So, the best I can do for you is https://blogtrottr.com/
If you find a wordpress plugin that will work, I’ll give it a try. None of the popular ones will work for authors.
Bob: yeah, I’m subscribed to that. But sometimes it’s nice to get an email alert pushed to me on a particular post I’m interested in, rather than having to monitor the entire feed. More so on say Chandoo or Peltier’s sites because there’s more mere mortals commenting there every day compared to us small bunch of nerds…present company excluded unless you care to admit otherwise ;-)
Dick: I wasn’t so much thinking of just myself. Which is unusual. I’m working up a post over at Chandoo.org called ‘How to read a blog’, that points out that most of the intelligent stuff happens in the comments, and it got me thinking why some blogs allow you to subscribe to a particular post, and others make you take everything under the sun.
Okay, I see, you want to subscribe to particular threads. I don’t find monitoring the whole feed too onerous on DDoE (except of course when we get a deluge, as in WTF!), not like Bacon Bits where the comments seem to consist entirely of a variety of pseudo-fawning spam such as … Hiya, You could have conducted an admirable job. I am going to certainly stumbleupon it and then for my element advocate to my pals. We are assured they are benefited from this blog.
As to what I am, I can live with either label.
I love Mike Alexander’s comments feed. It’s almost as risque as his actual blog!