It’s Evil Function election time: cast your votes so we can find out what the most evil Excel function is!
My vote has gone to INDIRECT (see my blog post here)
I will publish the results when we have got enough votes.
If you use, rely on, tell stories with, worry about, or operate in the advanced areas of Excel, then there’s a track designed just for you.
This is a unique opportunity to:
Use your opportunities, including two Panel discussions and Q&A sessions, throughout the two days to ask questions and discuss with the Excel MVPs and the industry experts.
The Excel Dev Team members will use this opportunity to learn from their customers, understand how you use Excel and get feedback on your Excel experience.
The ModelOff Meetup event delivers plenty of opportunities to mingle, learn from your peers, talk to the speakers and have fun.
At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.
There are 2 important things to note about this:
So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.
To try this out I made a suggestion (about calculation of course).
At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).
But you cannot calculate a single workbook.
This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.
My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.
So please vote for my suggestion if you think it’s a good idea!
It’s fun to argue about whether VLOOKUP or INDEX/MATCH is better, but to me that’s missing the point: they are both bad.
So I decided to design and build some better ones.
Here are some of the more-frequently mentioned VLOOKUP INDEX/MATCH problems
MEMLOOKUP ( Lookup_Value, Lookup_Array, Result_Col, Sort_Type, MemType_Name, Vertical_Horizontal )
The syntax is designed to make it easy to convert a VLOOKUP to MEMLOOKUP, but there are differences!
It’s always tempting to cram in more function (scope creep is universal), but if the result is too many parameters then it’s a mistake. So instead there is a whole family of these lookup functions that build on the MEMLOOKUP/MEMMATCH technology to provide the ultimate in flexibility and power whilst remaining efficient.
These functions are included in the 90 or so additional Excel functions built into FastExcel V3.
You can download the trial version from here.
I would be delighted to tell the Excel team how I built these functions and the algorithms they use.
By the way they are written as C++ multi-threaded functions in an XLL addin for maximum performance.
As some of you may know I have been working on developing Version 3 of my FastExcel product for more years than I care to remember.
First there was the VB6 blind alley, then 64-bit and multi-threaded calculation and that ribbon thing, then I detoured into learning C++ and XLLs, then there was the inevitable scope creep, (and my consulting customers kept asking me to do things, and then there was the sailing) … you get the picture.
Anyway I finally pushed the release button on June 4 2014. Yippee!!!
The initial reaction has been great (no doubt helped by the introductory offer of 50% off).
There are 3 major products in the FastExcel V3 family, targeted at different types of use scenarios.
The Profiler gives you a comprehensive set of tools focussed on finding and prioritising calculation bottlenecks. If your spreadsheet takes more than a few seconds to calculate you need FastExcel profiler to find out and prioritize the reasons for the slow calculation
FastExcel Manager contains tools to help you build, debug and maintain Excel workbooks.
SpeedTools provides you with a state-of-the-art tool-kit to help you speed up your Excel Calculations
Some of you will know that I have been developing (for the past 2 or more years!) some fast multi-threaded Excel functions using the XLL C++ interface.
After climbing this rather steep learning curve I am now very close to the final release of these functions.
FastExcel SpeedTools is designed to be a state-of-the-art set of tools to help you speed up calculation of slow workbooks:
The final SpeedTools Beta test is now live and I need more Beta Testers and feedback:
So download SpeedTools Beta 3 and tell me what you think!
The best 20 Beta test feedback reports received by the end of March will get their own exclusive SpeedTools coffee mug, (and a free license of course!).
Excel 2003 users get a toolbar to choose functions and launch the Function Wizard, and Excel 2007 and later users get 2 additional groups on the Formulas tab.