From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp1 ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id SEjYLdC3K1+hMQAA0tVLHw (envelope-from ) for ; Thu, 06 Aug 2020 07:57:04 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp1 with LMTPS id MBSmKdC3K1/seAAAbx9fmQ (envelope-from ) for ; Thu, 06 Aug 2020 07:57:04 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 4F85A940539 for ; Thu, 6 Aug 2020 07:57:04 +0000 (UTC) Received: from localhost ([::1]:40596 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1k3alr-00076d-4w for larch@yhetil.org; Thu, 06 Aug 2020 03:57:03 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:39864) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1k3ZKJ-0007JF-K4 for emacs-orgmode@gnu.org; Thu, 06 Aug 2020 02:24:31 -0400 Received: from lonyayantikvarium.hu ([151.236.7.126]:42526 helo=www1.lonyayantikvarium.hu) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1k3ZKH-000271-FJ for emacs-orgmode@gnu.org; Thu, 06 Aug 2020 02:24:31 -0400 Received: from x200s.lan (92-249-187-23.pool.digikabel.hu [92.249.187.23]) by www1.lonyayantikvarium.hu (Postfix) with ESMTPSA id 8B9F5401DB for ; Thu, 6 Aug 2020 08:24:23 +0200 (CEST) Message-ID: <5bce53e44fd04641e977a3ea52ed7d521670a678.camel@ralovich.hu> Subject: table formula, empty field, duration From: Kristof Ralovich To: emacs-orgmode@gnu.org Date: Thu, 06 Aug 2020 08:24:23 +0200 Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.36.3-0ubuntu1 MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Received-SPF: pass client-ip=151.236.7.126; envelope-from=kristof@ralovich.hu; helo=www1.lonyayantikvarium.hu X-detected-operating-system: by eggs.gnu.org: First seen = 2020/08/06 02:24:24 X-ACL-Warn: Detected OS = Linux 3.1-3.10 X-Spam_score_int: -4 X-Spam_score: -0.5 X-Spam_bar: / X-Spam_report: (-0.5 / 5.0 requ) BAYES_05=-0.5, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-Mailman-Approved-At: Thu, 06 Aug 2020 03:54:20 -0400 X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Reply-To: kristof@ralovich.hu Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: "Emacs-orgmode" X-Scanner: scn0 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of emacs-orgmode-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=emacs-orgmode-bounces@gnu.org X-Spam-Score: -1.01 X-TUID: 2B8hNZQRITiZ Dear All, it appears to me that the concept of "empty field" is not defined for time durations, or it is quite different to how it is defined for numbers (using org-mode 9.1.9). In the first table, the empty row @6 produces an empty field for the sum ($5, first formula), that vmean is able to "skip" (second formula) and gives the expected result 8.25 (=33/4). #+TBLNAME: works_with_numbers | h | start | end | h | sum | h | h | |---+-------+-----+---+-----+------+---| | | 08 | 18 | | 10 | | | | | 11 | 15 | | 4 | | | | | 09 | 20 | | 11 | | | | | 10 | 18 | | 8 | | | | | | | | | | | |---+-------+-----+---+-----+------+---| | | | | | 33 | 8.25 | | #+TBLFM: $5=if(typeof($3-$2)==12, string(""), $3-$2);E::@>$5=vsum(@2..@-1); #+TBLFM: @>$6=vmean(@2$5..@-1$5); However, in the second table, if time durations are used, how can an empty field be represented? For an empty row @6 the current first formula always computes 00:00 as opposed to an empty field. Unfortunately, later, vmean includes 00:00 in the computation, instead of skipping it. That is why the second formula produces 06:29, instead of 08:06 for @>$6. Is there a way to force the first formula to produce a truly empty field? Or is there a way to tell vmean to skip 00:00 durations? #+TBLNAME: does_not_work_with_duration | h | start | end | h | sum | h | h | |---+-------+-------+---+-------+-------+---| | | 08:54 | 18:01 | | 09:07 | | | | | 11:45 | 15:50 | | 04:05 | | | | | 09:20 | 20:23 | | 11:03 | | | | | 10:25 | 18:37 | | 08:12 | | | | | | | | 00:00 | | | |---+-------+-------+---+-------+-------+---| | | | | | 32:27 | 06:29 | | #+TBLFM: $5=if(typeof($3-$2)==12, string(""), $3-$2);EU::@>$5=vsum(@2..@-1);U #+TBLFM: @>$6=vmean(@2$5..@-1$5);U Thank you for your help! Kristof