Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

On 31/05/2013 at 22:10, Johnny Rosenberg <> wrote:

I obviously misunderstood the whole concept, so how is it supposed to be

Like that:

(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 

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:
Best regards
Mirosław Zalewski

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.