openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Johnny Rosenberg <gurus.knu...@gmail.com>
Subject Array formulas: maximum value – two conditions
Date Fri, 31 May 2013 20:10:14 GMT
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


Mime
View raw message