From mboxrd@z Thu Jan 1 00:00:00 1970 From: tsd@tsdye.com (Thomas S. Dye) Subject: Re: table formula help... Date: Fri, 05 Dec 2014 21:37:06 -1000 Message-ID: References: <87ppbxfi5f.fsf@ericabrahamsen.net> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:33841) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Xx9vn-00014e-GM for emacs-orgmode@gnu.org; Sat, 06 Dec 2014 02:37:37 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1Xx9vh-0004hP-2N for emacs-orgmode@gnu.org; Sat, 06 Dec 2014 02:37:31 -0500 Received: from gproxy6-pub.mail.unifiedlayer.com ([67.222.39.168]:57941) by eggs.gnu.org with smtp (Exim 4.71) (envelope-from ) id 1Xx9vg-0004ek-Rf for emacs-orgmode@gnu.org; Sat, 06 Dec 2014 02:37:24 -0500 In-Reply-To: <87ppbxfi5f.fsf@ericabrahamsen.net> (Eric Abrahamsen's message of "Sat, 06 Dec 2014 13:42:52 +0800") List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Eric Abrahamsen Cc: emacs-orgmode@gnu.org Aloha Eric, One approach would normalize the =3Dcounts=3D table=3D 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 =3DN=3D conditional on =3DUnit=3D and group by =3DTranslator=3D. I can't write the query off the top of my head. hth, Tom Eric Abrahamsen 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 | > +----------------+--------+-------+------------------+ > | =E5=87=A4=E5=87=B0 | | 84 | Austin Woerner | > | =E7=8E=8B=E8=A1=80 | 6633 | | Eric Abrahamsen | > | =E8=B5=B5=E6=B0=8F=E5=AD=A4=E5=84=BF | 16984 | | Canaan Mo= rse | > | =E5=B1=B1=E9=AC=BC=E6=95=85=E5=AE=B6 | | 24 | Lucas Kle= in | > | =E5=AF=82=E9=9D=99=E4=BD=95=E5=85=B6=E6=B7=B1=E6=B2=89 | | = 10 | Lucas Klein | > > And the second: > > #+NAME: payments > #+CONSTANTS: prose=3D0.7 poetry=3D10 > | 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 > > > --=20 Thomas S. Dye http://www.tsdye.com