Today I published a new version of the library jxxcarlson/elm-spreadsheet. While still rudimentary, it has an improved API in which formulas are more like the ones we are familiar with from Excel. For a demo, see cayatex.lamdera.app. The package is here.

Here is an example of the raw text of a spreadsheet

```
100; 1.1; mul A1,A2
120; 1.4; mul B1,B2
140; 0.8; mul C1,C2
-; add A2:C2; add A3:C3
```

When parsed, evaluated and rendered, it gives you this:

```
100; 1.1; 110
120; 1.4; 168
140; 0.9; 126
-; 3.4; 404
```

The parse-eval-render pipeline can be run like this: `ss |> read |> eval |> print`

where `ss`

is the text of the spreadsheet.

### Evaluating a spreadsheet

A spreadsheet is an `Array2D`

of `Cell`

, where a `Cell`

consists of either data or a formula. A spreadsheet is *fully evaluated* if it has no formulas. The function below will evaluate the cell

at given row and column if it contains a formula, returning the updated spreadsheet. If the cell

contains data, the spreadsheet is returned unchanged.

```
evalCell : Int -> Int -> Cell -> Spreadsheet -> Spreadsheet
```

By mapping this function over a spreadsheet, we obtain a function

```
evalOnce : Spreadsheet -> Spreadsheet
```

Because formulas may refer to cells which themselves contain formulas, a single application of `evalOnce`

may not fully evaluate a spreadsheet. As a remedy, one can apply `evalOnce`

repeatedly, keeping track of the number of unevaluated cells. When this number does not

change from one step to another, we call it quits, returning the result of the last iteration. The code

for `eval`

is in the appendix.

This strategy – seeking a fixed point – was suggested to me by Brian Hicks. One can do more with this approach, for example, seeking approximate fixed points in the case of (for example) financial computations that are solved by iteration (or finding roots of functions, for something more geeky).

Not all spreadsheets can be fully evaluated. This is the case, for example, when there are circular references.

**Note.** One can imagine a more sophisticated evaluation strategy based upon the dependency graph of the formulas. However, the iterative method described here seems to work rather well.

### Appendix: Code

```
type alias State = { notEvaluated : Int, sheet : Spreadsheet }
eval : Spreadsheet -> Spreadsheet
eval sheet =
eval_ { notEvaluated = Array2D.length sheet
, sheet = evalOnce sheet } |> .sheet
eval_ : State -> State
eval_ { notEvaluated, sheet } =
let
sheet2 =
evalOnce sheet
notEvaluated2 =
countNotEvaluated sheet2
in
if notEvaluated2 == notEvaluated then
{ notEvaluated = notEvaluated, sheet = sheet }
else
eval_ { notEvaluated = notEvaluated2, sheet = sheet2 }
```