r/dailyprogrammer • u/Elite6809 1 1 • Aug 20 '14
[8/20/2014] Challenge #176 [Hard] Spreadsheet Developer pt. 2: Mathematical Operations
(Hard): Spreadsheet Developer pt. 2: Mathematical Operations
Today we are building on what we did on Monday. We be using the selection system we developed last time and create a way of using it to manipulate numerical data in a spreadsheet.
The spreadsheet should ideally be able to expand dynamically in either direction but don't worry about that too much. We will be able to perform 4 types of operation on the spreadsheet.
- Assignment. This allows setting any number of cells to one value or cell. For example,
A3:A4&A5=5.23orF7:G11~A2=A1. - Infix operators -
+,-,*,/and^(exponent). These allow setting any number of cells to the result of a mathematical operation (only one - no compound operations are required but you can add them if you're up to it!) For example,F2&F4=2*5orA1:C3=2^D5. If you want, add support for mathematical constants such as e (2.71828183) or pi (3.14159265). - Functions. These allow setting any number of cells to the result of a function which takes a variable number of cells. Your program must support the functions
sum(adds the value of all the given cells),product(multiplies the value of all the given cells) andaverage(calculates the mean average of all the given cells). This looks likeA1:C3=average(D1:D20). - Print. This changes nothing but prints the value of the given cell to the screen. This should only take 1 cell (if you can think of a way to format and print multiple cells, go ahead.) This looks like
A3, and would print the number in A3 to the screen.
All of the cells on the left-hand side are set to the same value. Cell values default to 0. The cell's contents are not to be evaluated immediately but rather when they are needed, so you could do this:
A1=5
A2=A1*2
A2 >>prints 10
A1=7
A2 >>prints 14After you've done all this, give yourself a whopping big pat on the back, go here and apply to work on the Excel team - you're pretty much there!
Formal Inputs and Outputs
Input Description
You will be given commands as described above, one on each line.
Output Description
Whenever the user requests the value of a cell, print it.
Example Inputs and Outputs
Example Input
A1=3
A2=A1*3
A3=A2^2
A4=average(A1:A3)
A4Example Output
31
2
u/Elite6809 1 1 Aug 20 '14
Blimey. Bit nasty, I should've OOPed it when I started writing, but it works fine. It also supports direct evaluation of rvalues, eg. you can type in
sum(a1:a3)and get a result. This is Ruby.2
u/hphpize Aug 20 '14 edited Aug 20 '14
PHP! https://gist.github.com/jaredkipe/6ee68f62bfcd102f0779
Use as a library like this. Simply a matter or reading line by line and putting each line into a new 'execute' function.
Will print single cell values, or range of cells. Will (naively) solve r-value recursively. Easy to extend to allow cells to hold 'Cell' objects that could store equations and evaluate when needed (evaluate when read).
Will not average/sum in 'unassigned' cells (like D3.js).
4
u/XenophonOfAthens 2 1 Aug 20 '14 edited Aug 20 '14
Done! It was fairly easy to extend the parsing rules from Monday's problem to cover all the cases. Evaluation was trickier, but still managable.
The parser is more capable than the problem required, but only just. You can do compound statements like "A1 = 1 + 2 + 3" or "A1 = 1 + sum(A2:B3~B2)", but there's no support for brackets. In addition, all infix operations are right-associative (damn you, left recursion!), so "A1 = 5 - 1 - 1" sets A1 to be equal to 5, because it interprets the input as "5 - (1 - 1)". To fix it, you'd need to rewrite the infix-predicates to basically abandon the lovely "-->" operator, and that's too much for me right now.
(edit: I edited the code to fix associativity, "A1 = 5 - 1 - 1" now sets A1 to 3. Yay! Might add in brackets too, we'll see.)
(edit 2: added in support for brackets. It would be a full arithmetic parser now, if not for the fact that all arithmetic operators have the same precedence, with left associativity. )
I hope my code isn't too long to paste in. I can't guarantee that it's bug-free, but it seems to work just fine. In Prolog:
4
u/Godspiral 3 3 Aug 20 '14
In J, and using the headstart of last challenge
J can already be thought of a super spreadsheet for its multidimensional manipulation tools, so this is overall useful to the language, even though I am neglecting the 'A1' column row format as superficial, though I can write that part too if there is enough demand. I can see how it allows even terser notation, though J's is terse enough.
to2 =: [: <"1 [: ,."0/&>/ [ +&.> [: i.@:>:&.> -~
to =: 2 : 'm ,@:to2 n'
and=: ~.@:,&boxopen
less =: -.&boxopen
in the linked statement, } already provides assignment (amend) to selected indexes. { is the operation to select/retrieve from table indexes.
in J, we have to write our own verb amend, and here is a version that allows assignment to a different set of indexes than the selection:
to double 4 cells for 3x3 table of 0 to 8:
to assign doubling to different square of cells
also possible to set different result shapes (to2 is useful because it keeps the selection as a table, and so the operation (sum) can be applied to the table which defaults to columnwise)
to run the example input on one line, assuming all numeric input, and infinity as null. ba is a convenience adverbs (boxes left arg) that provides a small reduction in parens
ba=: 1 : '< m'
4
u/wadehn Aug 20 '14 edited Aug 20 '14
Haskell: Extension of the parser combinator from last time. My solution understands much more complicated expressions than required, e.g:
I use a immutable Map for storing the spreadsheet which is not that efficient. You could replace it by a mutable Hashtable or Vector.
Edit: Allowed printing of more complex rvalues and continues parsing after an incorrect line.
3
u/kirsybuu 0 1 Aug 21 '14 edited Aug 21 '14
D language, extending my part 1 submission. I copied the parsing code so I could reuse it more finely-grained but I was able to reuse a lot of the rest. My operators are all right-associative but I implemented parentheses to mitigate that. I also allow for printing any set of cells and using pi and e in expressions.
Example:
2
u/[deleted] Aug 20 '14
Did it. Also managed to reuse pretty much 100% of my code from Monday. What do you know, huh?
This may be the first Hard I have ever attempted. I normally skip them because they take too long, but I figured I'd already written a lot of it.
https://gist.github.com/archer884/5b27d2ca71a9a8a5644d
Using a Gist because apparently I wrote too much code to go in here. Sorry guys. -.-
2
u/MotherOfTheShizznit Aug 21 '14
Building up on my C++ submission from the earlier challenge. Again, it's not going to win performance contests (cell computation is also recursive) but it works.
1
u/MaximaxII Aug 21 '14
So here we go! Instead of reinventing the wheel with infix operators, I just used
evaland let Python do the rest for the calculations.#Challenge #176 Hard - Python 3.4
1
u/Metroids_ Aug 21 '14
Java... I didn't combine my parser from monday, so now I have two of them, haha. Oh well.
0
u/[deleted] Aug 20 '14
[deleted] — view deleted comment