14
\$\begingroup\$

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:

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

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

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

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

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

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

  7. 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.

\$\endgroup\$
4
  • \$\begingroup\$ Is submitted an answer in Excel/VBA disallowed? Expand array to cells, evaluate. \$\endgroup\$ Apr 6, 2017 at 17:15
  • \$\begingroup\$ @carusocomputing If you are not just letting Excel do its thing, then feel free to post. \$\endgroup\$
    – Adám
    Apr 7, 2017 at 5:08
  • \$\begingroup\$ Can we have cells be zero-based? As in, R0C0? \$\endgroup\$ Apr 7, 2017 at 15:57
  • \$\begingroup\$ @ConorO'Brien No, it has to be one of the two conventions used by Excel et al. \$\endgroup\$
    – Adám
    Apr 9, 2017 at 4:42

6 Answers 6

6
\$\begingroup\$

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:

  1. Does not require =.
  2. The left most cell of second row is A2.
  3. Does not require any prefix or suffix.
  4. "" (Empty String) to denote empty cell.
  5. JavaScript.
  6. JavaScript.
  7. 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]]));

\$\endgroup\$
9
  • \$\begingroup\$ Very nice. Here: 🍪. \$\endgroup\$
    – Adám
    Mar 27, 2017 at 13:51
  • \$\begingroup\$ Any reason you can't use /\w\d/g for your regex? \$\endgroup\$
    – powelles
    Mar 27, 2017 at 18:23
  • \$\begingroup\$ @powelles \w matches digits too, and \d matches 0 too, both things you don't want in this case. /[A-I][1-9]/g should work though \$\endgroup\$
    – LarsW
    Mar 27, 2017 at 18:44
  • \$\begingroup\$ @LarsW /\w\d/g produces the exact same results in the test cases. Also using a template literal in the eval portion could save a handful of bytes. \$\endgroup\$
    – powelles
    Mar 27, 2017 at 19:55
  • \$\begingroup\$ @powelles yes, but the test cases may be incomplete. Anyway, I assumed the chosen rules need to be followed; but they may just be input restrictions (or both), so that can be the problem too \$\endgroup\$
    – LarsW
    Mar 27, 2017 at 20:01
4
\$\begingroup\$

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
                .';'
            );
        }
    );
}
\$\endgroup\$
5
  • \$\begingroup\$ +1 Very nice; 🍪. You say standard arithmetics, but really any reasonable PHP expression would work, no? \$\endgroup\$
    – Adám
    Mar 27, 2017 at 12:46
  • 1
    \$\begingroup\$ @Adám Now that you mention it ... as long as it´s lowercase, yes. Though max(range(A1,A3)) might be confusing. :D \$\endgroup\$
    – Titus
    Mar 27, 2017 at 16:15
  • 1
    \$\begingroup\$ Nice golf took me quite long to find anything: $d=$c;preg_match($x="#[A-Z](.)#",$c=$c[0] to preg_match($x="#[A-Z](.)#",$c=($d=$c)[0] should save 1 byte. \$\endgroup\$
    – Christoph
    Mar 28, 2017 at 7:42
  • \$\begingroup\$ Would it be possible to include a Try it online! link? \$\endgroup\$
    – Adám
    Mar 28, 2017 at 8:22
  • 1
    \$\begingroup\$ @Christoph took me a new approach to fix the second example ... and saved a lot in the process ... plus further golfing \$\endgroup\$
    – Titus
    Mar 28, 2017 at 14:23
3
\$\begingroup\$

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])
  1. Formulas are S-expressions
  2. A2
  3. No, (+ A1 A2) is fine
  4. nil and false work as empty cells, but empty string do not
  5. Clojure
  6. 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!

\$\endgroup\$
0
3
\$\begingroup\$

Mathematica, 119 115 95 bytes

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

Choices:

  1. No prefix.
  2. R2C1 style.
  3. No prefix or suffix.
  4. "" for empty cells.
  5. Mathematica.
  6. 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.

\$\endgroup\$
6
  • \$\begingroup\$ Cool. I thought that it would take a little longer than that... Any way to try this? \$\endgroup\$
    – Adám
    Mar 27, 2017 at 9:47
  • \$\begingroup\$ @Adám Unfortunately, it doesn't seem to work in Mathics, so without a copy of Mathematica, probably not. \$\endgroup\$ Mar 27, 2017 at 9:51
  • \$\begingroup\$ Still waiting for the builtin... \$\endgroup\$
    – YSC
    Mar 27, 2017 at 14:22
  • \$\begingroup\$ @YSC Excel, 0 bytes. \$\endgroup\$
    – Adám
    Mar 27, 2017 at 14:57
  • \$\begingroup\$ @Adám That would not be competing per the rules of this challenge. Also, I think Martin deserves a cookie. \$\endgroup\$ Apr 5, 2017 at 13:02
3
\$\begingroup\$

APL (Dyalog), 51 bytes

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

  2. A2

  3. None

  4. Empty string

  5. APL

  6. 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.

\$\endgroup\$
2
  • 1
    \$\begingroup\$ Your explanations are always beautiful, thanks, makes me want to learn APL beyond what little I know from reading these answers. \$\endgroup\$ Apr 6, 2017 at 17:12
  • \$\begingroup\$ @carusocomputing Thank you. I'll be happy to assist you. Feel free to questions and requests in the APL chat room. I'll help you get set up too if you need that. \$\endgroup\$
    – Adám
    Apr 7, 2017 at 5:07
1
\$\begingroup\$

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:

  1. =

  2. A2

  3. none

  4. ""

  5. Python 2.7

  6. 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!
\$\endgroup\$
15
  • \$\begingroup\$ You didn't add import of re in your answer. Also it doesn't pass this input: s=[[ 3, 4,"max(A1,B1)"],[ 2, 5,"A2**B2"],[ "", "","C1+C2"]] \$\endgroup\$ Mar 28, 2017 at 7:50
  • \$\begingroup\$ The input format is [[ 3, 4,"=max(A1,B1)"],[ 2, 5,"=A2**B2"],[ "", "","=C1+C2"]] That is there as a part of headers! Check it on the try it online link! \$\endgroup\$ Mar 28, 2017 at 7:54
  • 1
    \$\begingroup\$ It should be included in byte count. Just check out other python answers on this site \$\endgroup\$ Mar 28, 2017 at 7:57
  • \$\begingroup\$ Is it? I'm net to codeglof. Thanks for the comment. Will add it. \$\endgroup\$ Mar 28, 2017 at 7:57
  • \$\begingroup\$ As far as the other answers are considered, they havent included the input! I've edit with the bye count of import! \$\endgroup\$ Mar 28, 2017 at 7:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.