From mboxrd@z Thu Jan 1 00:00:00 1970 From: Nick Subject: spreadsheet table limitations, specifically summing hours? Date: Fri, 14 May 2010 11:27:57 +0100 Message-ID: <4BED25AD.3080203@letterboxes.org> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Return-path: Received: from [140.186.70.92] (port=60212 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1OCs7r-0008Kv-LM for Emacs-orgmode@gnu.org; Fri, 14 May 2010 06:28:16 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.69) (envelope-from ) id 1OCs7l-0002Zp-R8 for Emacs-orgmode@gnu.org; Fri, 14 May 2010 06:28:15 -0400 Received: from udon.noodlefactory.co.uk ([80.68.88.167]:46846) by eggs.gnu.org with esmtp (Exim 4.69) (envelope-from ) id 1OCs7l-0002Z0-IM for Emacs-orgmode@gnu.org; Fri, 14 May 2010 06:28:09 -0400 Received: from 87-194-154-6.bethere.co.uk ([87.194.154.6] helo=[192.168.0.100]) by udon.noodlefactory.co.uk with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1OCs7i-0003yM-J8 for Emacs-orgmode@gnu.org; Fri, 14 May 2010 11:28:06 +0100 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Emacs-orgmode@gnu.org Hi, Having used orgmode to track my TODO list and related working hours, I have been trying to use it to work out my invoices. Although the spreadsheet is quite neat, I have been having trouble getting it to do some things. In particular, after reading the manual I initially thought I could, but empirically find I can't: a) write formulas like below, which both use and assign to column names, e.g. $total=$vat+$fee b) Use underscores or hyphens (or less surprisingly, spaces) in column names Correct? Or is there something I've missed? Anyway, I can work around those, by using column numbers, and not using underscores etc.; but I'm still trying to discover a workable way of summing the hours. There seem to be two problems; c) Summing HH:MM values (which org-table-sum seems to manage - although oddly without including the minutes - but not vsum) d) writing a formula to sum items above it, ideally summing values in between two hlines [Later: I solved d) as by putting the sums in a separate hline section and using vsum(@-I..@-II), see below] | | week ending | hh:mm | Fee | VAT | Total | | ! | week | hours | fee | vat | total | |---+-------------+--------+-------+-------+-------| | | 27/12/09 | 10:19h | | | | | | 10/01/10 | 3:00h | | | | | | 17/01/10 | 18:50h | | | | | | 24/01/10 | 13:00h | | | | |---+-------------+--------+-------+-------+-------| | # | 25/01/10 | ??:??h | ??.?? | ??.?? | ??.?? | |---+-------------+--------+-------+-------+-------| | | 31/01/10 | 19:47h | | | | | | 07/02/10 | 19:19h | | | | | | 14/02/10 | 23:14h | | | | |---+-------------+--------+-------+-------+-------| | # | 22/02/10 | ??:??h | ??.?? | ??.?? | ??.?? | |---+-------------+--------+-------+-------+-------| #+TBLFM: $hours=vsum(@-I..@-II)::$fee=$hours*$hourlyrate;%.2f::$vat=$total*$vatrate;%.2f::total=$vat+$fee #+CONSTANTS: vatrate=0.175 hourlyrate=35 [Note: my mail client wraps the +TBLFM line] I suspect there maybe a way of using elisp to sum the hours, but on a cursory inspection of the source, org-table-sum seems not to be designed for the job, and I don't see another one I could use off-the-shelf. (I'm using org-mode v6.35 in emacs 23.0.91.1) Thanks in retrospect for earlier answers and in advance future help. Cheers, Nick