calc array expression using result of min fn

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

calc array expression using result of min fn

Gary Aitken
I'm having trouble getting an array expression to work in calc.
What I want is this:

   { COUNT( (MIN(B2:B10; C2:C10) = B2:B10) ) }

That is, count the number of rows in which the minimum across columns
B and C is in column B.

The expression
   MIN(B3; C3) = B3
works fine when entered in an individual row...

Any help would be much appreciated.
I do know to use <ctrl><shift><enter>

Thanks,

Gary

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: calc array expression using result of min fn

Oliver Brinzing
Hi Gary,

have you tried something like this:

  {=SUM(IF(B2:B10<=C2:C10;1;0))}

for example, according to
https://ask.libreoffice.org/en/question/20404/how-do-i-make-an-array-formula-out-of-a-function-that-normally-takes-array-arguments/

the max()/min() functions do not work this way.

Regards
Oliver

Am 14.08.2018 um 17:23 schrieb Gary Aitken:

> I'm having trouble getting an array expression to work in calc.
> What I want is this:
>
>    { COUNT( (MIN(B2:B10; C2:C10) = B2:B10) ) }
>
> That is, count the number of rows in which the minimum across columns
> B and C is in column B.
>
> The expression
>    MIN(B3; C3) = B3
> works fine when entered in an individual row...
>
> Any help would be much appreciated.
> I do know to use <ctrl><shift><enter>
>
> Thanks,
>
> Gary
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: calc array expression using result of min fn

Gary Aitken
On 08/14/18 11:24, Oliver Brinzing wrote:

> have you tried something like this:
>
> {=SUM(IF(B2:B10<=C2:C10;1;0))}

Unfortunately, that won't work in my case; or rather, that's what I was
trying to avoid.
My example was a simplification; the actual need is for something like
   MIN(B2:B10 ; C2:C10 ; D2:D10 ; ...)
One could nest if's, but that gets cumbersome with many columns, as in:
   =SUM( IF(B2:B201<C2:C201) * IF(B2:B201<D2:D201) * IF(B2:B201<F2:F201) )

> for example, according to
> https://ask.libreoffice.org/en/question/20404/how-do-i-make-an-array-formula-out-of-a-function-that-normally-takes-array-arguments/
>
the max()/min() functions do not work this way.

Is there a list someplace of functions that do and do not work with
array expressions?

Thanks for the reply,

Gary

>> I'm having trouble getting an array expression to work in calc.
>> What I want is this:
>>
>> { COUNT( (MIN(B2:B10; C2:C10) = B2:B10) ) }
>>
>> That is, count the number of rows in which the minimum across
>> columns B and C is in column B.
>>
>> The expression MIN(B3; C3) = B3 works fine when entered in an
>> individual row...
>>
>> Any help would be much appreciated. I do know to use
>> <ctrl><shift><enter>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: calc array expression using result of min fn

Oliver Brinzing
Hi Gary,

 > Is there a list someplace of functions that do and do not work with
 > array expressions?

maybe this can help:

https://wiki.openoffice.org/wiki/Documentation/How_Tos/Using_Arrays
https://help.libreoffice.org/Calc/Array_Functions


Regards
Oliver

Am 14.08.2018 um 22:15 schrieb Gary Aitken:

> On 08/14/18 11:24, Oliver Brinzing wrote:
>
>> have you tried something like this:
>>
>> {=SUM(IF(B2:B10<=C2:C10;1;0))}
>
> Unfortunately, that won't work in my case; or rather, that's what I was
> trying to avoid.
> My example was a simplification; the actual need is for something like
>    MIN(B2:B10 ; C2:C10 ; D2:D10 ; ...)
> One could nest if's, but that gets cumbersome with many columns, as in:
>    =SUM( IF(B2:B201<C2:C201) * IF(B2:B201<D2:D201) * IF(B2:B201<F2:F201) )
>
>> for example, according to
>> https://ask.libreoffice.org/en/question/20404/how-do-i-make-an-array-formula-out-of-a-function-that-normally-takes-array-arguments/ 
>>
>>
> the max()/min() functions do not work this way.
>
> Is there a list someplace of functions that do and do not work with
> array expressions?
>
> Thanks for the reply,
>
> Gary
>
>>> I'm having trouble getting an array expression to work in calc. What I want is this:
>>>
>>> { COUNT( (MIN(B2:B10; C2:C10) = B2:B10) ) }
>>>
>>> That is, count the number of rows in which the minimum across columns B and C is in column B.
>>>
>>> The expression MIN(B3; C3) = B3 works fine when entered in an individual row...
>>>
>>> Any help would be much appreciated. I do know to use <ctrl><shift><enter>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]