Aloha Eric, One approach would normalize the =counts= table= and use a SQL query to generate the payments table.
#+name: counts | id | Piece | N | Unit | Translator | | 1 | foo | 84 | line | bar | | 2 | baz | 6633 | char | foobar | The query would sum over =N= conditional on =Unit= and group by =Translator=. I can't write the query off the top of my head. hth, Tom Eric Abrahamsen <e...@ericabrahamsen.net> writes: > I never seem to use the spreadsheet unless it's some horribly > complicated thing I don't know how to calculate... Can someone lend me a > hand? > > I'm calculating payment rates for contributors (actually translators) to > a magazine. > > I've got two tables: The first is essentially a table of contents, > listing pieces with their character count (prose), or line count > (poetry), plus who translated it. > > The second is a list of translators, with their total character/line > count, and how much they're owed. > > I'm having a hell of a time getting the column formulas right: > specifically referencing one table from another. The first table looks > like this: > > #+NAME: counts > | Piece | Chars | Lines | Translator | > +----------------+--------+-------+------------------+ > | 凤凰 | | 84 | Austin Woerner | > | 王血 | 6633 | | Eric Abrahamsen | > | 赵氏孤儿 | 16984 | | Canaan Morse | > | 山鬼故家 | | 24 | Lucas Klein | > | 寂静何其深沉 | | 10 | Lucas Klein | > > And the second: > > #+NAME: payments > #+CONSTANTS: prose=0.7 poetry=10 > | Translator | Total Chars | Total Lines | Payment | > |------------------+-------------+-------------+---------| > | Austin Woerner | | | | > | Eric Abrahamsen | | | | > | Canaan Morse | | | | > | Lucas Klein | | | | > > In this second table, the formula I want for the "Total Chars" column > is: > > "Set each row of $2 in table payments to the sum of all numbers in $2 of > remote(counts), if $2 is not empty, and if $4 of remote(counts) is equal > to $1 of table payments." > > The formula for "Total Lines" would be exactly the same, but with the > $2s all switched to $3. I can handle adding up the amounts! > > Is it possible to iterate over all the rows in another table like this? > Is there a better way I could be arranging my tables? > > Thanks for any tips! > > Eric > > > -- Thomas S. Dye http://www.tsdye.com
