2013/6/1 Mirosław Zalewski <[email protected]>: > On 31/05/2013 at 22:10, Johnny Rosenberg <[email protected]> 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: [email protected] For additional commands, e-mail: [email protected]
