Re: [libreoffice-users] Array formulas: maximum value – two conditions

2013/6/1 Mirosław Zalewski <miniopl@poczta.onet.pl>:
> On 31/05/2013 at 22:10, Johnny Rosenberg <gurus.knugum@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 to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted