spreadsheet table limitations, specifically summing hours?

[Nick <oinksocket@letterboxes.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