You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Johnny Rosenberg <gu...@gmail.com> on 2013/05/31 22:10:14 UTC
Array formulas: maximum value – two conditions
Column C: Name
Column D: Date
Column H: Points
I want to see the highest point for the name in P3 for dates in this
year (2013).
I can do highest score for the name that's stored in P3, regardless of the year:
{=MAX(IF(C2:C10000=P3; H2:H10000; 0))}
Result: 612.
Expected result: 612.
And the highest score for the this year, regardless of name:
{=MAX(IF(YEAR(D2:D10000)=YEAR(TODAY()); H2:H10000; 0))}
Result: 596.
Expected result: 596.
I tried the following:
{=MAX(IF(AND(YEAR(D2:D10000)=YEAR(TODAY());C2:C10000=P3); H2:H10000; 0))}
Result: 0.
Expected result: 588.
I obviously misunderstood the whole concept, so how is it supposed to be done?
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [libreoffice-users] Array formulas: maximum value – two conditions
Posted by Johnny Rosenberg <gu...@gmail.com>.
2013/6/1 Mirosław Zalewski <mi...@poczta.onet.pl>:
> On 31/05/2013 at 22:10, Johnny Rosenberg <gu...@gmail.com> wrote:
>
>> I obviously misunderstood the whole concept, so how is it supposed to be
>> done?
>
> Like that:
> {=MAX((C1:C10000=P3)*(YEAR(D1:D10000)=YEAR(TODAY()))*E1:E10000)}
>
> (C1:C10000=P3) will act like IF statement. It will return 10000 elements array
> containing 1 (if cell matches P3) or 0 (otherwise).
> The same goes to second statement, which compares years with current year.
> We do not do anything to last column values.
>
> This formula will evaluate to multiplication of arrays containing 0, 1 and
> original numbers. If both conditions are true, it will not change value in E
> column. If at least one condition is not true, it will effectively zero entire
> row.
>
> Then these numbers (original E values and zeroes) are fed to MAX function.
>
> BUT array formulas on large datasets are far from being efficient. Using
> database function might be better idea (basically, database functions are as
> fast as array formulas or faster than them).
>
> The basics of database functions are:
> - your range of data must be structured; first row is considered header (there
> should be text briefly describing content of column)
> - you must repeat your header in range containing conditions. Each column must
> be present at least once.
> - in condition range, cells in one row represents conjunction
> - in condition range, each row represents alternative
>
> The tricky part here is, your date column contains not only year, but also
> month and day. So you can't really put "2013" into criteria range and call it
> a day. You must search for dates between 1.1.2013 and 31.12.2013.
> If your date column contains cells with date type, then they are internally
> represented by number of days since 30 December 1899. So we can get around the
> issue with DATEVALUE.
>
> Explaining what to put where would take some time, so I put spreadsheet
> online. You can download it here:
> <http://minio.komunikatory.pl/pliki/array-and-dmax.ods>
> --
> Best regards
> Mirosław Zalewski
Thank you, and all the others who replied, for valuable information. I
also like the idea to reply with an actual spreadsheet, so I don't
need to translate all the cell functions to Swedish… :)
I think I was right in my first post: I didn't completely understand
the concept. Thank you guys for explaining.
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: [libreoffice-users] Array formulas: maximum value – two conditions
Posted by Mirosław Zalewski <mi...@poczta.onet.pl>.
On 31/05/2013 at 22:10, Johnny Rosenberg <gu...@gmail.com> wrote:
> I obviously misunderstood the whole concept, so how is it supposed to be
> done?
Like that:
{=MAX((C1:C10000=P3)*(YEAR(D1:D10000)=YEAR(TODAY()))*E1:E10000)}
(C1:C10000=P3) will act like IF statement. It will return 10000 elements array
containing 1 (if cell matches P3) or 0 (otherwise).
The same goes to second statement, which compares years with current year.
We do not do anything to last column values.
This formula will evaluate to multiplication of arrays containing 0, 1 and
original numbers. If both conditions are true, it will not change value in E
column. If at least one condition is not true, it will effectively zero entire
row.
Then these numbers (original E values and zeroes) are fed to MAX function.
BUT array formulas on large datasets are far from being efficient. Using
database function might be better idea (basically, database functions are as
fast as array formulas or faster than them).
The basics of database functions are:
- your range of data must be structured; first row is considered header (there
should be text briefly describing content of column)
- you must repeat your header in range containing conditions. Each column must
be present at least once.
- in condition range, cells in one row represents conjunction
- in condition range, each row represents alternative
The tricky part here is, your date column contains not only year, but also
month and day. So you can't really put "2013" into criteria range and call it
a day. You must search for dates between 1.1.2013 and 31.12.2013.
If your date column contains cells with date type, then they are internally
represented by number of days since 30 December 1899. So we can get around the
issue with DATEVALUE.
Explaining what to put where would take some time, so I put spreadsheet
online. You can download it here:
<http://minio.komunikatory.pl/pliki/array-and-dmax.ods>
--
Best regards
Mirosław Zalewski
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Array formulas: maximum value two conditions
Posted by Brian Barker <b....@btinternet.com>.
At 08:18 01/06/2013 +0530, Kadal Amutham wrote:
>The IF statement will expect a condition to test, not and another AND.
Sorry, but by itself this is simply untrue, so cannot be the answer
to the problem. The first argument of the IF() function needs to be
a logical expression; its value - TRUE or FALSE - determines which of
the other two arguments is used. That expression may often be a
simple test, but the result of an AND() function is just as acceptable to IF().
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Array formulas: maximum value – two conditions
Posted by Kadal Amutham <vk...@gmail.com>.
The IF statement will expect a condition to test, not and another AND.
With Warm Regards
V.Kadal Amutham
919444360480
914422396480
On 1 June 2013 01:40, Johnny Rosenberg <gu...@gmail.com> wrote:
> Column C: Name
> Column D: Date
> Column H: Points
>
> I want to see the highest point for the name in P3 for dates in this
> year (2013).
>
> I can do highest score for the name that's stored in P3, regardless of the
> year:
> {=MAX(IF(C2:C10000=P3; H2:H10000; 0))}
> Result: 612.
> Expected result: 612.
>
> And the highest score for the this year, regardless of name:
> {=MAX(IF(YEAR(D2:D10000)=YEAR(TODAY()); H2:H10000; 0))}
> Result: 596.
> Expected result: 596.
>
> I tried the following:
> {=MAX(IF(AND(YEAR(D2:D10000)=YEAR(TODAY());C2:C10000=P3); H2:H10000; 0))}
> Result: 0.
> Expected result: 588.
>
> I obviously misunderstood the whole concept, so how is it supposed to be
> done?
>
>
> Johnny Rosenberg
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
>
Re:Array formulas: maximum value – two conditions
Posted by johnny smith <ka...@krovatka.su>.
On Fri, 31 May 2013 20:10:14 -0000, Johnny Rosenberg
<gu...@gmail.com> wrote:
> Column C: Name
> Column D: Date
> Column H: Points
>
> I want to see the highest point for the name in P3 for dates in this
> year (2013).
>
> I tried the following:
> {=MAX(IF(AND(YEAR(D2:D10000)=YEAR(TODAY());C2:C10000=P3); H2:H10000; 0))}
> Result: 0.
> Expected result: 588.
>
> I obviously misunderstood the whole concept, so how is it supposed to be
> done?
substituting ordinary mathematical multiplication for logical conjunction
did the trick for me, which is as follows:
{=max(if(if(year(d2:d10000)=year(today());1;0)*if(c2:c10000=p3;1;0);h2:h10000;0))}
i think the origin of the problem is that {=and(a1:a3)} is interpreted as
{=and(a1;a2;a3)}.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org