Evaluate a simple spreadsheet

Question

Rules

1. No cell range references (A2:B3).

2. Maximum 9 rows and 9 columns.

3. No circular references or formula errors.

4. Empty cells evaluate to 0.

5. Data are numbers only, but may be taken as strings.

6. Formulas are strings.

Implementation choices

You must state your choices in these matters:

7. Require formulas to be prefixed with any single character, e.g. = – or not.

8. The leftmost cell of the second row is A2 or R2C1, as per the two conventions used by Excel et al.

9. Require any single-character pre- or suffix in cell references, e.g. $ – or not.

10. One of null, empty string, empty list, etc., (but not 0) to represent empty cells.

11. Language of your submission (spreadsheet managers are not allowed).

12. Language for the formulas (may differ from the above).*

13. Brownie points or cookies for explaining your solution.

Examples

Choices: 7: =; 8: A2; 9: none; 10: ""; 12: Excel Formula Language

In:

[[       2, 3],
 ["=A1+B1",""]]

Out:

[[2,3],
 [5,0]]

In:

[[       2,"=A1+B2"],
 ["=A1+B1",      ""]]

Out:

[[2,2],
 [4,0]]

In:

[[ 3, 4,"=A1*B1"],
 [ 2, 5,"=A2*B2"],
 ["","","=C1+C2"]]

Out:

[[3,4,12],
 [2,5,10],
 [0,0,22]]

In:

[["","=2*B2*B3"     ,""],
 [ 3,"=A1+(A2+C2)/2", 2],
 [ 1,"=C1+(A3+C3)/2", 5]]

Out:

[[0,15,  0],
 [3, 2.5,2],
 [1, 3  ,5]]

---

^{* The formula language must be PPCG admissible, but you only have to support cell references plus criteria 3 and 4 there, wiz. addition and primeness determination.}

Answer 1

JavaScript, 125 112 105 bytes

To use, add f= at the beginning and invoke like f(argument).

a=>a.map(b=>b.map(p=>+p?p:p?eval(p.replace(/[A-I][1-9]/g,m=>`a[${m[1]-1}][${(m.charCodeAt(0)-65)}]`)):0))

Choices:

7. Does not require =.
8. The left most cell of second row is A2.
9. Does not require any prefix or suffix.
10. "" (Empty String) to denote empty cell.
11. JavaScript.
12. JavaScript.
13. Cookies. 🍪🍪🍪

Explanation:

This solution iterates over all the cells of the worksheet (each element of the sub-arrays of the given array) and if non-empty String is found, replaces its cell references with the corresponding references in terms of the given array and evaluates the expression with eval() (yeah, that evil thing that haunts you in your nightmares). This solution assumes that the constants provided in the input array are of Integer type.

Test Cases

f=a=>a.map(b=>b.map(p=>+p?p:p?eval(p.replace(/[A-I][1-9]/g,m=>`a[${m[1]-1}][${(m.charCodeAt(0)-65)}]`)):0))

console.log(f([[1,2,3],["A1+B1+C1",10,11]]));
console.log(f([[1,2,5,4,6,89,0],[0,1,2,3,"A2+A1",5,6]]));
console.log(f([[1,2,4,5],["A1/B1*C1+A1+Math.pow(5,B1)",2,3,4]]));

Answer 2

PHP, 265 263 259 258 257 240 224 222 213 202 196 bytes

featuring array_walk_recursive, a recursive anonymous function and preg_replace_callback:

function f(&$a){array_walk_recursive($a,$p=function(&$c)use($a,&$p){eval('$c='.preg_replace_callback("#R(.)C(.)#",function($m)use($a,&$p){$d=$a[$m[1]-1][$m[2]-1];$p($d);return$d;},$c?:0).';');});}

or

function f(&$a){array_walk_recursive($a,$p=function(&$c)use($a,&$p){eval('$c='.preg_replace_callback("#R(.)C(.)#",function($m)use($a,&$p){return$p($a[$m[1]-1][$m[2]-1]);},$c?:0).';');return$c;});}

operates on input: call by reference. Test it online.

• no expression prefix
• reference format R2C1, no prefix
• anything falsy for empty cell
• evaluates any (lowercase) PHP expression, including all arithmetics

breakdown (first version)

function f(&$a)
{
    array_walk_recursive($a,                    # walk through elements ...
        $p=function(&$c)use($a,&$p){            # use array $a and recursive $p
            eval('$c='.                             # 3. evaluate expression
                preg_replace_callback('#R(.)C(.)#', # 2. replace references with ...
                function($m)use($a,&$p){
                    $d=$a[$m[1]-1][$m[2]-1];            # $d=content of referenced cell
                    $p($d);                             # recursive evaluation
                    return$d;                           # return $d
                },$c?:0)                            # 1. replace empty with 0
                .';'
            );
        }
    );
}

Answer 3

Clojure, 263 281 bytes

Oh damn without that apply map vector the result is in transpose, as A2 is alphabetically before B1.

#(apply map vector(partition(count(% 1))(for[v(vals(loop[C(into{}(mapcat(fn[i r](map(fn[j v][(str j i)(or v"0")])"ABCDEFGHI"r))(rest(range))%))P[]](if(= C P)C(recur(into(sorted-map)(for[[k v]C][k(reduce(fn[r[K V]](clojure.string/replace r K V))v C)]))C))))](eval(read-string v)))))

Examples:

(def f #(...))

(f [["2" "3"]["(+ A1 B1)" nil]])
([2 3] [5 0])

(f [[nil ,"(* 2 B2 B3)"                ,nil],
    ["3" ,"(+ A1 (/ (+ A2 C2) 2))"     ,"2"],
    ["1" ,"(-> A3 (+ C3) (/ 2) (+ C1))","5"]])
([0 15N 0] [3 5/2 2] [1 3 5])

7. Formulas are S-expressions
8. A2
9. No, (+ A1 A2) is fine
10. nil and false work as empty cells, but empty string do not
11. Clojure
12. S-expressions (Clojure + any built-in macros)

An example of thread first macro:

(macroexpand '(-> A3 (+ C3) (/ 2) (+ C1)))
(+ (/ (+ A3 C3) 2) C1)

Starting value of C in the loop is a hash-map, keys are cell names and values are original values. Then all cell references are replaced by contents of referenced cells until we have converged (Previous = Current), then cells are evaluated and the flat structure is partitioned back into a nested list.

Would be cool to find a solution where A1, A2 etc. are actually callable functions, then (* 2 B2 B3) could be rewritten to (* 2 (B2) (B3)) and executed.

Try it online!

Answer 4

Mathematica, 119 115 95 bytes

(m=#/.""->0)//.s_String:>ToExpression@StringReplace[s,"R"~~x_~~"C"~~y_:>"m[["<>{x,",",y,"]]"}]&

Choices:

7. No prefix.
8. R2C1 style.
9. No prefix or suffix.
10. "" for empty cells.
11. Mathematica.
12. Mathematica. Arbitrary arithmetic expressions that don't use variables of the form RxCy and don't have side effects should work.

Explanation

(m=#/.""->0)

We start by replacing all empty strings in the input (#) with zeros and storing the result in m, because we'll need this again in another place.

...//.s_String:>...

Repeatedly replace any remaining string s with the following...

...StringReplace[s,"R"~~x_~~"C"~~y_:>...]

Match any substrings of the form RxCy in s and replace them with...

..."m[["<>{x,",",y,"]]"}

Which gives m[[x,y]], which uses x and y as indices into the matrix m.

...ToExpression@...

Finallz, evaluate this string as a Mathematica expression.

Answer 5

APL (Dyalog), 51 bytes

⍎¨({((⍴⍵)↑⍉⎕A∘.,1↓⎕D),¨'←',¨⍵}{0::⍵⋄×≢⍵:⍕⍎⍕⍵⋄0}¨)⍣≡

7. None

8. A2

9. None

10. Empty string

11. APL

12. APL

Try it online!

⍎¨ Evaluate each cell of the result from

(…)⍣≡ continuous application of the following two functions until nothing more changes

 { first anonymous function to be applied…

  0 on any
  :: error:
   ⍵ return the argument unmodified

  ⋄ now try;

  ⍵ if the argument's
  ≢ tally:
  × is positive
  : then:
   ⍕ stringify
   ⍎ the evaluated
   ⍕ stringified
   ⍵ argument

  ⋄ else;

  0 return zero

 }¨ … to each cell

 { second anonymous function to be applied…

  '←',¨ prepend an assignment arrow to each cell of
  ⍵ the argument

  (…),¨ prepend the following to each cell of that

   1↓ drop the first of
   ⎕D the string of all Digits (i.e. zero)

   ⎕A With all the letters of the Alphabet going down,
   ∘., make a concatenation table (with the remaining digits going right)

   ⍉ transpose (to get increasing numbers down, progressing letters right)

   (…)↑ take the upper left submatrix with the size of…

    ⍴ the size of
    ⍵ the argument

 } … to the the previous function's result.

Answer 6

Python 2 273,265,263, 259 bytes

import re
def g(m):x=m.group();return's[%d][%d]'%(int(x[1])-1,ord(x[0])-65)
e=enumerate
while'='in`s`:
    for x,i in e(s):
        for i,j in e(i):
            try:s[x][i]=0if not j else float(eval(re.sub('\w\d',g,str(j)).strip('=')))
            except:pass

Try it online!

Choices:

7. =

8. A2

9. none

10. ""

11. Python 2.7

12. Python expressions

Basic explanation:

For every formula in the sublist, substitute it with the corresponding list (that is, for B1 s[0][1]) index and evaluate the result!

• -4 bytes by changing str() to backticks!