# 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

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.