emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
* spreadsheet table limitations, specifically summing hours?
@ 2010-05-14 10:27 Nick
  2010-05-14 14:44 ` Nick Stokoe
  2010-05-14 14:49 ` Nick
  0 siblings, 2 replies; 10+ messages in thread
From: Nick @ 2010-05-14 10:27 UTC (permalink / raw)
  To: Emacs-orgmode

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

^ permalink raw reply	[flat|nested] 10+ messages in thread

end of thread, other threads:[~2010-05-17 19:29 UTC | newest]

Thread overview: 10+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2010-05-14 10:27 spreadsheet table limitations, specifically summing hours? Nick
2010-05-14 14:44 ` Nick Stokoe
2010-05-15 14:27   ` Carsten Dominik
2010-05-14 14:49 ` Nick
2010-05-14 21:59   ` Stephan Schmitt
2010-05-14 23:40     ` Nick
2010-05-15  6:24     ` Carsten Dominik
2010-05-15  6:31   ` Carsten Dominik
2010-05-17 13:34     ` oinksocket
2010-05-17 19:29       ` Štěpán Němec

Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).