how to exclude certain row values from calculation in a table

how to exclude certain row values from calculation in a table

[Uwe Brauer]

Hi all

Please look at 

|   |  Price |
|---+--------|
| / |  10.98 |
|   |  11.90 |
|   |  19.98 |
|   |  13.79 |
|   |  29.97 |
|   |  18.98 |
|   |  13.79 |
|   |  11.90 |
|   |  24.28 |
|---+--------|
|   | 155.57 |
#+TBLFM: @11$2=vsum(@I$2..@II$2);f2

I would like to exclude the first value from the sum by adding some mark
in that row.

Of course, I also could use 
|   |  Price |
|---+--------|
| / |  10.98 |
|---+--------|
|   |  11.90 |
|   |  19.98 |
|   |  13.79 |
|   |  29.97 |
|   |  18.98 |
|   |  13.79 |
|   |  11.90 |
|   |  24.28 |
|---+--------|
|   | 144.59 |
#+TBLFM: @11$2=vsum(@II$2..@III$2);f2

But suppose the value I'd like to exclude would be 29.97, then I would
need some reordering, I prefer to avoid.

Any idea how to exclude row values in a column?


Regards

Uwe Brauer 


-- 
I strongly condemn Hamas heinous atrocities on Israel, especially the despicable pogroms.
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military. 
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine. 
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/

Re: how to exclude certain row values from calculation in a table

[Bruno Barbier]

Hi Uwe,

Uwe Brauer <oub@mat.ucm.es> writes:

> Hi all
>
> Please look at 
>
> |   |  Price |
> |---+--------|
> | / |  10.98 |
> |   |  11.90 |
> |   |  19.98 |
> |   |  13.79 |
> |   |  29.97 |
> |   |  18.98 |
> |   |  13.79 |
> |   |  11.90 |
> |   |  24.28 |
> |---+--------|
> |   | 155.57 |
> #+TBLFM: @11$2=vsum(@I$2..@II$2);f2
>
> I would like to exclude the first value from the sum by adding some mark
> in that row.
...
>
> Any idea how to exclude row values in a column?

You could use 'vmask' to mask the values you don't want.
Here is an example using vmask:

   |                |  Price |
   |----------------+--------|
   | 1              |  10.98 |
   |                |  11.90 |
   |                |  19.98 |
   |                |  13.79 |
   | 1              |  29.97 |
   |                |  18.98 |
   |                |  13.79 |
   |                |  11.90 |
   |                |  24.28 |
   |----------------+--------|
   |                | 155.57 |
   | Total unmarked | 114.62 |
   | Total marked   |  40.95 |
   | Check all      |     OK |
   #+TBLFM: @11$2=vsum(@I$2..@II$2);f2
   #+TBLFM: @12$2=vsum(vmask(map(<0 = #1>, @I$1..@II$1), @I$2..@II$2));f2NE
   #+TBLFM: @13$2=vsum(vmask(map(<1 = #1>, @I$1..@II$1), @I$2..@II$2));f2NE
   #+TBLFM: @14$2=string(if(@12$2+@13$2 = @11$2, "OK", "Oops!"))

Bruno

Re: how to exclude certain row values from calculation in a table

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1448 bytes --]

Hi Bruno


> Hi Uwe,

> Uwe Brauer <oub@mat.ucm.es> writes:

> ...

> You could use 'vmask' to mask the values you don't want.
> Here is an example using vmask:

>    |                |  Price |
>    |----------------+--------|
>    | 1              |  10.98 |
>    |                |  11.90 |
>    |                |  19.98 |
>    |                |  13.79 |
>    | 1              |  29.97 |
>    |                |  18.98 |
>    |                |  13.79 |
>    |                |  11.90 |
>    |                |  24.28 |
>    |----------------+--------|
>    |                | 155.57 |
>    | Total unmarked | 114.62 |
>    | Total marked   |  40.95 |
>    | Check all      |     OK |

>    #+TBLFM: @11$2=vsum(@I$2..@II$2);f2
>    #+TBLFM: @12$2=vsum(vmask(map(<0 = #1>, @I$1..@II$1), @I$2..@II$2));f2NE

>    #+TBLFM: @13$2=vsum(vmask(map(<1 = #1>, @I$1..@II$1), @I$2..@II$2));f2NE
>    #+TBLFM: @14$2=string(if(@12$2+@13$2 = @11$2, "OK", "Oops!"))

Very cool, thank you very much indeed, this is very helpful

Uwe 




-- 
I strongly condemn Hamas heinous atrocities on Israel, especially the despicable pogroms.
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military. 
I support the NATO membership of Ukraine.
I support the EU membership of Ukraine. 
https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]