Four functions I'd like to see in the next version of Excel

Originally published on my company internal blog.

Excel provides a bewildering array of built-in functions for all manner of scientific and statistical analysis. It even lets users write their own functions. But no matter how flexible a tool is, users will inevitably want immediate, specific functionality ​that meets their needs now. Here are some Excel functions I'd love to see.

ACTUALPERCENTAGE()

Copy-and-paste a percentage from a text source, and the value is stored is a number such as 50. But Excel stores actual percentages as a fraction (0.5) and merely dislays them as a number with a % sign. This means that 50, when imported from a text source, displays as 5000%. That's definitely wrong.

This useful function looks at the context of your number, including the number of decimal places that you've set, and decides whether it should be divided by a hundred or not.

CELLFORMAT()

Conditional formatting lets you set the format of the cell based on the cell's contents. For example, cells containing negative numbers can be highlighted in red.

But what if you want to change the contents of the cell based on its formatting? For example, what if all red cells should have their values weighted? That's where CELLFORMAT() comes in. It returns a description of the format of the cell against which you can do useful comparisons and adjust your business logic accordingly.

=IF(CELLFORMAT()="Red with yellow stripes", "Safe to bathe", "Do not go in the water")

Remember: It's considered best-practice to design your spreadsheets so that business logic depends upon the formatting of the cell.

ABACUS()

Excel already includes a ROMAN() function which converts numbers into Roman figures. But what if your Excel user isn't an ancient Roman, but instead is a Mesopotamian or Egyptian from 2,500BC? That's where ABACUS() comes in: it converts numerals to a visual representation of beads. An optional second argument returns the result in the Sumerian base-60 system.

CPP()

Everyone knows that VBA is a toy language used only by failed software engineers who've turned into ops managers through sheer bad luck. What Excel really needs is a way of executing a proper language, within the constraints of a normal formula (ie 32,767 characters long). CPP() executes C++ code within the context of the spreadsheet. Spreadsheet objects are available in the same way that they are to VBA.

=CPP("#include <iostream> ; #include <worksheet> ; int main() { cout << \"Hello, \" << Worksheet.Cells(\"A1\").Value(); return 0; };")

See? Simple, clear, powerful.