2013/6/1 Mirosław Zalewski <mini...@poczta.onet.pl>: > On 31/05/2013 at 22:10, Johnny Rosenberg <gurus.knu...@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+unsubscr...@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