openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "johnny smith" <ka...@krovatka.su>
Subject Re: Array formulas: maximum value – two conditions
Date Fri, 31 May 2013 21:35:36 GMT
On Fri, 31 May 2013 20:10:14 -0000, Johnny Rosenberg  
<gurus.knugum@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


Mime
View raw message