5g:Testing
Description
5g functions must be thoroughly tested before releasing to others. Releasing a function that can fail can spell disaster for those who use our functions and for our reputation. Thorough testing is one of the best ways for ensuring our functions won't fail.
Learning Objectives
At the end of this course, learners will know:
Strategies for testing
Prerequisites
RAND()
RANDARRAY()
RANDBETWEEN()
Discussion
Testing is a proven technique for driving out errors. There are many things, and many ways to test. Let us discuss a few.
Inline Help Test
One of the first things, and easiest things to test is our function's inline help. Just type = followed by the function's name and don't enter any arguments. Examine the displayed inline help text.
Is it for the correct function (we didn't copy paste it from another function without changing it did we)?
Is it aligned (did we forget a delimiter somewhere)?
Does it include everything?
Is everything spelled correctly?
Scenario Tests
It is impossible to test for all scenarios for any problem that involves just a handful of numeric or date variables. What we can do is use sampling to create random values that reasonably represent the values a user might enter. An automated version of this approach is shown in the video at right and the workbook used to automate the tests is available here: TestTemplate workbook.
The basic approach is to use one of Excel's random number generators: RAND, RANDARRAY, and RANDBETWEEN, to create sample values for each of our function's arguments and place the randomize values where they can be accessed by our 5g function and a non-LAMBDA, non-dynamic array solution that does the same thing. Compare results. If both methods produce identical results, the function may be correct. Repeat many times to confirm.
TestTemplate.xlsm
To use TestTemplate.xlsm follow these steps.
Open TestTemplate.xlsm
Immediately save the workbook under the same name as the function we are testing.
Load the function we want to test. We strongly urge loading functions from GitHub Gists using Microsoft's free add-in, Advanced Formula Environment.
On the Inline Help Test worksheet (tab) enter the function's name with no arguments into the cell A6 which has a light gray fill (input style)
Review the Inline Help to be sure it contains all required elements
Is it for this function?
Is it properly aligned?
Does the first line contains the function's name and its syntax?
Does the second line contain the Webpage address (if any exists)?
Does the next line contain the correct version date.
Do the next lines contain each function argument name with either (Required) or (Optional) and a description.
Does the last line contain an example (if practical).
On the Function Test worksheet (tab) starting in cell A21 (first row in the Inputs table (tblInputs) enter the Argument Name, Type, Range, and UOM (units of measure) for each value to generate. The type of value is determined by entries in the Type column (see below). The range sets the lower and upper limit of values to generate. Type can be one of these values
Integer - Whole numbers will be generated
Number - Numbers with decimal places will be generated
Percentage - Number will be generated and divided by 100.
Date - Dates will be generated
Evaluate - The formula in the Range column (without the = sign) will generate the value
Choose - A comma separated list of values limits what may be selected.
After making all entries in the Inputs table, click Run Tests. This will create the Test Results table and add one row to it.
Starting two columns to the right of the last column of the Test Results table, setup your test data.
There is no one way to setup the tests. Some 5g function process arrays by rows. Some by columns. Some by individual cells. Some user timelines. How we test our functions is how we expect them to be used. What follows will be for a 5g function that processes an array that is above it.
Below our test data, enter our 5g function
Below the 5g function, enter an alternative means of calculation what our 5g function calculates.
Below the alternative means enter a function that compares our 5g function's results to the alternative means results. In many cases, this formula works for this purpose: =ABS( 5g cell - alternative cell) < .0001 This subtracts the two method's results and if the difference is within tolerance, we accept it is being the same.
In cell B7, enter the formula: =AND( row from step 10) If all tests in step 10 equate to TRUE (passed), the overall test passes. If any equate to FALSE, the overall test fails.
Set the number of tests to run to 100 and click Run Tests.
If any test fails, fix the problem and try again. Repeat step 12 until at least 100 tests pass.
Here are some options for randomized values:
Number
Use this for inputs that require numbers with decimals such as currency amounts.
=LowValue + RAND() * (HighValue - LowValue)
Where LowValue is the lowest value that could possibly make sense for your function and HighValue is the highest.
Integer
Use this for inputs that require numbers with decimals such as currency amounts.
=RANDBETWEEN( LowValue, HighValue)
Where LowValue is the lowest value that could possibly make sense for your function and HighValue is the highest.
Date
Use this for inputs that require Dates.
=RANDBETWEEN( "2024-01-01", "2031-12-31")
Replace 2024-01-01 with the earliest date that could possibly make sense for your function and replace 2031-12-31 with the highest. Make sure leap year dates are included (if applicable)
Choose
Use this for inputs that can only be one of a few choices such as timing intervals like Day, Week, Month, Quarter, Year.
= CHOOSECOLS( TEXTSPLIT( "D,W,M,Q,Y", ","), RANDBETWEEN(1,5))
Replace "D,W,M,Q,Y" with all possible choices. Replace 5 with the number of possible choices.
Edge Tests
These tests check for things the end user might do, and probably shouldn't. Examples include invalid dates, negative amounts where only positive amounts are allowed, text entered for numeric inputs, codes entered that are not valid, etc. Create tests for these cases. For invalid inputs, 5g Functions should fail with one of Excel's error conditions.
Orientation Test
We should remember that dynamic arrays can spill horizontally and vertically. If we want our function to work no matter which way the array spills, we must test it against both orientations.
Array vs Range Test
Dynamic arrays can spill into cells, or create an array within our function. In general, 5g functions are for ranges. But some can be used inside formulas and never spill into a range. This can cause some Excel functions to fail. The xIFS functions (SUMIFS, COUNTIFS, etc.) only work over ranges. If we intend our function to work inside a formula, we should test that it will work in that environment.
Summary
Testing is one of the best ways to uncover errors in our functions. The hard part of testing is thinking about all the ways our function might be used. When testing functions in timelines, we should consider how our function should respond when the start date is before the timeline start and the end date is after the timeline end.
Good testing requires that we not only think about what the user should do, but what the user shouldn't do, but could.