Adding A New High-Level Spreadsheet Function To AFC

A high-level function, in this context, is one that can be defined by rewriting it in terms of other, lower-level Excel functions. Doing this makes it immediately available to the constant folder, and to all the different numeric types supported by AFC.

As an example of a high-level function we will look at how I added COMBIN() to the list of supported spreadsheet functions. So for the rest of this page, we will assume COMBIN() has not been implemented yet.

Note: You must have read adding a new primitive function to understand this section.

Docs and Tests

As with ABS, we start with documentation (announcing COMBIN() in the release notes), and the citable test cases:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Expected =IF(Q1,"Expected","FAILED!")	Actual	Inputs							# of Inputs	Name	Highlight	Excel says	Skip for	Custom check		true =AND(Q2:Q10000)
450	1	1 =COMBIN(C450,D450)	0	0						2	COMBIN	COMBIN
451	1	1 =COMBIN(C451,D451)	1	0						2
452	1	1 =COMBIN(C452,D452)	1	1						2
453	1	1 =COMBIN(C453,D453)	2	0						2
454	2	2 =COMBIN(C454,D454)	2	1						2
455	1	1 =COMBIN(C455,D455)	2	2						2
456	1	1 =COMBIN(C456,D456)	3	0						2
457	3	3 =COMBIN(C457,D457)	3	1						2
458	3	3 =COMBIN(C458,D458)	3	2						2
459	1	1 =COMBIN(C459,D459)	3	3						2
460	10	10 =COMBIN(C460,D460)	10	1						2
461	252	252 =COMBIN(C461,D461)	10	5						2
462	120	120 =COMBIN(C462,D462)	10	7						2

Again, as explained in the topic on the reference test sheets, you also have to update the corresponding .ods and .yaml files. If you forget, running the tests will tell you.

Parsing

Making COMBIN() known to the parser is again just like what we did for ABS(). The only point worth noting is that we need two arguments:

|	"COMBIN" fun2( Function.COMBIN )

Rewriting It

Now we get to the interesting part. We rewrite COMBIN(n,k) in terms of lower-level Excel and AFC functions. From the Excel help file we see that COMBIN(n,k) is

$$\frac{n!}{k!\; (n-k)!}$$

where, in Excel, the factorial n! is FACT(n). So we would like to define, in AFC:

COMBIN(n,k) = FACT(n) / FACT(k) / FACT(n - k)

However, we should also test for error cases, like the following:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Expected =IF(Q1,"Expected","FAILED!")	Actual	Inputs							# of Inputs	Name	Highlight	Excel says	Skip for	Custom check		true =AND(Q2:Q10000)
463	!NUM:FE	#NUM! =COMBIN(C463,D463)	0	1						2			#NUM!
464	!NUM:FE	#NUM! =COMBIN(C464,D464)	1	-1						2			#NUM!
465	!NUM:FE	#NUM! =COMBIN(C465,D465)	-1	1						2			#NUM!

This shows that we need to add a few tests for invalid arguments. We can do this with IF, as you can see further below.

Rewrite Rule

To define a rewrite rule in AFC, you need to add the rule to the file

components/compiler/src/build/resources/org/formulacompiler/compiler/internal/build/rewriting/rewrite.rules

This is done as follows:

rewrite combin( n, k ) =
	if OR( n < 0, k < 0, n < k ) then err( "#NUM! because n < 0 or k < 0 or n < k in COMBIN" )
	else if n = k then 1
	else if k = 1 then n
	else
		FACT(n) / FACT(k) / FACT(n-k)

(Note that I have added two optimizations for n = k and k = 1 here.)

The definition should look straightforward.

Running ant build regenerates the rewriter so it includes the new definition. (In the IDE, you can simply run the class RewriteRulesCompiler, which we just modified, to regenerate.)

Parameter Reuse

When you access a parameter more than once (as we do above), AFC’s rule compiler automatically inserts a let around the parameter in question. This ensures that parameters are not evaluated more than once. For example, an application like

COMBIN( A1+A2, B1-B2 )

results in inlined code like

int n, k;
return ... fac((n = A1+A2)) / fac((k = B1-B2)) / fac(n - k) ...

where fac() is the runtime support function for FACT().

No Runtime

A rewrite rule always generates code directly into the computation. There is no option to move support code into the runtime, as there is for primitive functions (see fac() above). This may change in a future release.