Home | Quick Start | Documentation | Download | Support | Contribute

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! \left(n-k\right)!}$

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.