Using Calc unit conversion functions in an Org Spreadsheet

Using Calc unit conversion functions in an Org Spreadsheet

[Simon Pugnet]

[-- 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 --]

Re: Using Calc unit conversion functions in an Org Spreadsheet

[Eric S Fraga]

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

Re: Using Calc unit conversion functions in an Org Spreadsheet

[Simon Pugnet]

[-- 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 --]

Re: Using Calc unit conversion functions in an Org Spreadsheet

[Michael Heerdegen]

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.

Re: Using Calc unit conversion functions in an Org Spreadsheet

[Simon Pugnet]

[-- 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 --]