Your Chariot Awaits
Don’t get me wrong here… I still despise its interface, but the internals of Excel 2007 continue to impress me.
They keep adding functions that I have been carping about for years.
Newest example: IFERROR
In a lot of funky (and even some relatively conventional) situations, you need to test if an expression is an error and only use the result of the expression if it is not an error.
For example, I need to see if the leftmost six characters in a cell are numeric (each cell is either blank or has text and numbers combined). If they are numeric, I want to convert them to a number and display them; otherwise, I want to display “NO”.
That used to look like:
=IF(NOT(ISERROR(VALUE(LEFT(A2,6)))),VALUE(LEFT(A2,6)),"NO")
Now, it can be:
=IFERROR(VALUE(LEFT(A2,6)),"NO")
The prior problem was the requirement to repeat the main expression twice, once for error testing and once to display the result. I had a lot of multi-line formulas because of that mess. Now it’s squeaky clean.