*Using Calc unit conversion functions in an Org Spreadsheet@ 2023-11-15 10:52 Simon Pugnet2023-11-15 16:07 ` Eric S Fraga 2023-11-16 7:28 ` Michael Heerdegen 0 siblings, 2 replies; 5+ messages in thread From: Simon Pugnet @ 2023-11-15 10:52 UTC (permalink / raw) To: help-gnu-emacs [-- Attachment #1: Type: text/plain, Size: 1755 bytes --] Hi everyone, I'm currently trying to use Org mode's spreadsheet functionality to calculate quotes for clients. For example, I might have a table like this: - | Task | Time estimate | Rounded hours | CPH | Cost | |---------+---------------+---------------+-------------+---------------| | Task #1 | 1 hr + 35 min | 1.75 hr | 50 GBP / hr | 87.5 GBP | | Task #2 | 0 hr + 20 min | 20 min | 50 GBP / hr | 16.666667 GBP | #+TBLFM: $3=usimplify(ceil($2*4)/4)::$5=usimplify($4*$3) What I'm trying to do here is to round the time estimate to the nearest 15 minutes, then multiply that figure by the cost per hour (CPH) to produce the final cost. The problem with the example above is with the ~usimplify~ calls. In the first case, the units get simplified to hours (1.75 hr) and in the second it gets simplified to minutes (20 min). The rounding calculation (ceil(x * 4) / 4) therefore only works when the units are hours as the minutes have no need of rounding. You can see that above: 1 hr + 35 min gets rounded correctly to 1.75 hr, but 0 hr + 20 min does not (it becomes 20 min, but should be 30 min). I'm therefore trying to replace ~usimplify~ with a function which converts units, i.e. to force the value to be in hours. However I can't find such a function. I found ~math-convert-units~ which seems to do exactly what I need however I can't get it to work and it's not documented. Passing a string expression such as ~(math-convert-units "45 min" 'hr)~ causes the error "Lisp error: (wrong-type-argument number-or-marker-p hr)". Any help would be greatly appreciated! Kind regards, -- Simon Pugnet https://www.polaris64.net/ PGP key fingerprint: 3BF7 85DE 162C 00C8 FB4D A6FD BA13 59A8 2C0B 3EF9 [-- Attachment #2: signature.asc --] [-- Type: application/pgp-signature, Size: 861 bytes --] ^ permalink raw reply [flat|nested] 5+ messages in thread

*Re: Using Calc unit conversion functions in an Org Spreadsheet2023-11-15 10:52 Using Calc unit conversion functions in an Org Spreadsheet Simon Pugnet@ 2023-11-15 16:07 ` Eric S Fraga2023-11-17 7:32 ` Simon Pugnet 2023-11-16 7:28 ` Michael Heerdegen 1 sibling, 1 reply; 5+ messages in thread From: Eric S Fraga @ 2023-11-15 16:07 UTC (permalink / raw) To: help-gnu-emacs How about the following: --8<---------------cut here---------------start------------->8--- | Task | Time estimate | alternative | Rounded hours | CPH | Cost | |---------+---------------+-------------+---------------+-------------+---------------| | Task #1 | 1 hr + 35 min | 1.5 hr | 1.75 hr | 50 GBP / hr | 87.5 GBP | | Task #2 | 0 hr + 20 min | 0.25 hr | 20. min | 50 GBP / hr | 16.666667 GBP | #+TBLFM: $3=round($-1/(15 min))*hr/4;u::$4=usimplify(ceil($2*4)/4)::$6=usimplify($5*$4) --8<---------------cut here---------------end--------------->8--- (see new column "alternative") -- Eric S Fraga via gnus (Emacs 30.0.50 2023-09-14) on Debian 12.2 ^ permalink raw reply [flat|nested] 5+ messages in thread

*Re: Using Calc unit conversion functions in an Org Spreadsheet2023-11-15 10:52 Using Calc unit conversion functions in an Org Spreadsheet Simon Pugnet 2023-11-15 16:07 ` Eric S Fraga@ 2023-11-16 7:28 ` Michael Heerdegen2023-11-17 7:36 ` Simon Pugnet 1 sibling, 1 reply; 5+ messages in thread From: Michael Heerdegen @ 2023-11-16 7:28 UTC (permalink / raw) To: help-gnu-emacs Simon Pugnet <simon@polaris64.net> writes: > I found ~math-convert-units~ which seems to do exactly what I need > however I can't get it to work and it's not documented. Passing a > string expression such as ~(math-convert-units "45 min" 'hr)~ causes > the error "Lisp error: (wrong-type-argument number-or-marker-p hr)". Hmm - yes, this function expects to be called like this (the example converts 0.35 hr to 21 min): (math-convert-units '(* (float 35 -1) (var hr var-hr)) '(var min var-min)) ==> (* (float 21 1) (var min var-min)) Unless you want to convert your input into the internal format of math expressions, maybe it is easier to first divide the input by 1hr, then simplify the (now unitless) result, and continue with that? Michael. ^ permalink raw reply [flat|nested] 5+ messages in thread

*Re: Using Calc unit conversion functions in an Org Spreadsheet2023-11-15 16:07 ` Eric S Fraga@ 2023-11-17 7:32 ` Simon Pugnet0 siblings, 0 replies; 5+ messages in thread From: Simon Pugnet @ 2023-11-17 7:32 UTC (permalink / raw) To: Eric S Fraga;+Cc:help-gnu-emacs [-- Attachment #1: Type: text/plain, Size: 714 bytes --] Eric S Fraga <e.fraga@ucl.ac.uk> writes: > How about the following: > > > | Task | Time estimate | alternative | Rounded hours | CPH | Cost | > |---------+---------------+-------------+---------------+-------------+---------------| > | Task #1 | 1 hr + 35 min | 1.5 hr | 1.75 hr | 50 GBP / hr | 87.5 GBP | > | Task #2 | 0 hr + 20 min | 0.25 hr | 20. min | 50 GBP / hr | 16.666667 GBP | > #+TBLFM: $3=round($-1/(15 min))*hr/4;u::$4=usimplify(ceil($2*4)/4)::$6=usimplify($5*$4) > > (see new column "alternative") Thanks Eric, that works nicely. Thanks too for reminding me about the units simplification mode (;u), that saves calls to usimplify. Kind regards, Simon [-- Attachment #2: signature.asc --] [-- Type: application/pgp-signature, Size: 861 bytes --] ^ permalink raw reply [flat|nested] 5+ messages in thread

*2023-11-16 7:28 ` Michael HeerdegenRe: Using Calc unit conversion functions in an Org Spreadsheet@ 2023-11-17 7:36 ` Simon Pugnet0 siblings, 0 replies; 5+ messages in thread From: Simon Pugnet @ 2023-11-17 7:36 UTC (permalink / raw) To: Michael Heerdegen;+Cc:help-gnu-emacs [-- Attachment #1: Type: text/plain, Size: 1292 bytes --] Michael Heerdegen <michael_heerdegen@web.de> writes: > Simon Pugnet <simon@polaris64.net> writes: > >> I found ~math-convert-units~ which seems to do exactly what I need >> however I can't get it to work and it's not documented. Passing a >> string expression such as ~(math-convert-units "45 min" 'hr)~ causes >> the error "Lisp error: (wrong-type-argument number-or-marker-p hr)". > > Hmm - yes, this function expects to be called like this (the example > converts 0.35 hr to 21 min): > > (math-convert-units > '(* (float 35 -1) (var hr var-hr)) > '(var min var-min)) > > ==> (* (float 21 1) (var min var-min)) > > Unless you want to convert your input into the internal format of math > expressions, maybe it is easier to first divide the input by 1hr, then > simplify the (now unitless) result, and continue with that? > > Michael. Thanks Michael, yes I think as you say the easiest way is to remove the units first, then explicitly convert back to the desired units afterwards. Thanks for the explanation of math-convert-units. Once I saw it I became convinced that this was the best way to achieve my goals, but the alternative of using math operations to modify the units seems cleaner. Thanks for your help! Kind regards, Simon [-- Attachment #2: signature.asc --] [-- Type: application/pgp-signature, Size: 861 bytes --] ^ permalink raw reply [flat|nested] 5+ messages in thread

end of thread, other threads:[~2023-11-17 7:36 UTC | newest]Thread overview:5+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2023-11-15 10:52 Using Calc unit conversion functions in an Org Spreadsheet Simon Pugnet 2023-11-15 16:07 ` Eric S Fraga 2023-11-17 7:32 ` Simon Pugnet 2023-11-16 7:28 ` Michael Heerdegen 2023-11-17 7:36 ` Simon Pugnet

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).