Strong Typing

Strong Typing is an add-in that allows formulas to be augmented with type meta data to help ensure formulas work as intended or to highlight logic issues with formulas in a spreadsheet.

  • Define any number of types
  • Create types automatically from existing worksheets
  • Create rules to define how types should be validated
  • Validate a whole worksheet or a smaller range
  • Report validation results
  • Export and share rule sets

What is the problem solved?

Huge numbers of people use Excel and it’s a testament to Excel’s ease of use that so many people feel able to use it. The problem is, Excel is easy to use because it only sees a list of numbers. Moreover, it makes assumptions on the user’s behalf which makes it really easy to do stupid things like add apples and oranges.

Why is it easy to make mistakes in Excel?

Suppose the purpose of a spreadsheet is to record and sum the sales of different types of apples and oranges: Bramley, Cox, Gala, Satsuma, Tangerine, etc. Excel relies on the user entering the numbers and then creating a formula to sum total apples and total apples. The intention of the user may be to create a formula to total all apple sales but Excel offers no help selecting the values to sum instead the user has to know. Of course in a simple example like this, there really is no issue. But in non trivial spreadsheets it has been shown that even highly skilled spreadsheet users will make at least one error. If in this trivial case the person who creates the formula total sales of apples also includes a value for the sales of a type of orange Excel will offer no alert to warn the user.

Excel also employs a technique called ‘coercion’. It may sound like something involved in subterfuge but the technique allows Excel to make a best guess at the user’s intent. The most obvious example is when a cell containing text is included in a range of cells that is summed. Excel does not try to add a number and text to report an error. Instead is ‘coerces’ the string value to an implicit value of zero. In many cases this is harmless – even preferred. But if the text looks like a number the user will be unaware the ‘number’ is not included in the total. To be fair modern versions of Excel will include a green triangle in the upper left corner when text looks like as number. But disabling this safeguard or defeating the safeguard, either accidentally or deliberately is trivial.

So what do you mean 'Strong Typing?

The same problems that affect Excel also affect programmers using some programming languages. ‘Dynamic’ languages like JavaScript and PHP also support coercion and, like Excel, are considered easier to use. Also like Excel, these programs are prone to errors. However more formal language like C++, Java or C# do not suffer these problems.

The difference between, say, JavaScript and Java is a type system. That is, in Java a programmer is able to defined ‘types’. ‘Fruit’ might be a type. ‘Apple’ and ‘Orange’ might be sub-types of the type Fruit. In languages like Java it is impossible to add values of type Apples and Oranges because compiler will prevent it and will report the error condition.

Now imagine being able to define types in Excel and assign them to cells so Excel is able to report when apples are added to oranges. That is strong typing.

It’s strong typing to distinguish it from the value type system that Excel, like dynamic languages has. Value types are ‘number’. ‘text’ and ‘yes/no’. These types cannot be extended in dynamic language.

Learn more about Strong Typing for Excel

There are two ways to explore Strong Typing further: watch the Getting Started video then Start your free trial today and use it for up to 10 days.

Please leave a comment

Copyright Lyquidity Solutions © 2021 · All Rights Reserved · Strongly typed spreadsheets · Strong Typing