Most functions in Excel are said to be “strict”, this means that all their parameters are used to work out the result. Take for example the function AVERAGE – it will give a result that is the average of all its arguments. So, AVERAGE(21/3,3+4,5*2) will work out each parameter and get 7, 7 and 10. It will then work out their average, that is (7+7+10)/3 which is 8.
In EQUS, the formula is visualized to show this:
You might think that functions should always be strict, but no. Take for example the function “PRODUCT”, this gives the result of multiplying each of its arguments together. In theory, there are cases when it need not be strict. Imagine the formula =PRODUCT(0,A3+5,A4*2^3), the zero for the first argument means that we know the result will be zero (zero times anything is zero).
So, regardless of what values are computed in A3 or A4 the answer will still be zero. In this example PRODUCT does not have to be strict for all it arguments. This short of knowledge is what is used to make efficient computations. However, in Excel you’ll find PRODUCT is strict, and PRODUCT(0,A3+5,A4*2^3) will be computed in full by Excel.
EQUS shows this in its visualization:
Interestingly there are also functions that should not be strict, in fact having non-strict functions in necessary in most computation and its adds significant power to systems like Excel. A common example of a purposefully non-strict function is the IF function.
IF is used to give one of two parameters as its result depending upon the value of another parameter. So with any use of IF, one of its parameters is in fact not used. As simple example of IF’s use would be to determine a category result from given a number. For instance, one might want to compute a result “service needed” when an odometer value is over 15000, and “no service needed” when it is not.
IF(E1 > 15000, “service needed”, “no service needed”)
If the cell E1 is greater than 15000 then, “service needed” is computed (and “no service needed” is not used). And the when E1 is not greater than 15000, it is the other way around and “service needed” is not used. The EQUS visualisation for IF shows the unused component faded out.
So the two examples just discussed look like, this:
A more complex example would be to compute tax due when there is tax threshold.
IF(G2 < 17500, 0, (G2 – 17500)*0.1)
Here, in our fictional example, no tax is due when G2 is below 17500 and anything over 17500 is taxed at 10%.
The EQUS visualizations for two different G2 values, shows the difference.
You’ll see with much of Microsoft Excel, the basic ideas are simple and easily combined. However, when combined they can quickly become hard to follow. For example, a real tax example in a single formula would require IF’s with parameters that also themselves include IF’s, in effect at least one IF is needed for each tax band.
Checking any complex formula is correct and doing what it is meant to is notoriously hard. However, the EQUS visualization helps by exposing many of the details of what is going on.