Great,
thank you for your elaborate answer
It works as expected.
Rob.
Op 26 apr. 2014, om 07:46 heeft Brian Barker het volgende geschreven:
> At 01:50 26/04/2014 +0200, Rob Jasper wrote:
>> I have a (generated) sheet with 5000 entries where in column H is a book
>> number between 1 and 7, and in column D there are years between 1852 and
>> 2014.
>> Both the booknumbers and the years are not sorted, and are expanded over
>> time. I want to have the min and max year of each individual book.
>>
>> In fact I need something like MINIF, but that does not seem to exist.
>> Something like MIN(D2:D5000 where H2:H5000 = 1) similar for MAX. How can I
>> accomplish this?
>
> Here's one idea:
>
> o In row 2 of a spare column, say column M, enter =$D2*($H2=COLUMN()-12) .
> o Fill this formula down column M.
> o Select M2 to M5000 and fill this across the following six columns - to
> column S.
> o For the minimum year for book 1, enter =MIN(IF(M2:M5000=0;9999;M2:M5000)) -
> but don't press Enter or click the green tick mark to complete the formula.
> Instead, press Ctrl+Shift+Enter. This creates an array formula, which will be
> shown in the Input Line surrounded by braces:
> {=MIN(IF(M2:M16=0;9999;M2:M16))} - but note that you cannot achieve the
> required effect by typing the braces yourself.
> o For the maximum year for book 1, use =MAX(M2:M5000) .
> o You can fill these minimum and maximum formulae (wherever they are) to the
> right across a further six columns to harvest the values for the other six
> book numbers.
>
> How does it work?
> o COLUMN() returns the column number of the cell in which its formula
> appears. For column M this is 13, so if we subtract 12 this provides the book
> number values 1 to 6 in columns M to S.
> o The logical expression $H2=COLUMN()-12 evaluates whether the book number is
> the one in question.
> o When this is multiplied by the year in D2, the logical value is interpreted
> as a number - 0 for FALSE and 1 for TRUE. So the result - in columns M to S -
> is the year if the row applies to the current book number and zero otherwise.
> o The IF() function creates a notional array of the years in the current
> column, but with zeroes replaced by 9999 (a sentinel value, larger than any
> likely year). The MIN() function then harvests the minimum year for the
> current book number, unfettered by those original zeroes.
> o The MAX function harvests the maximum year for the current book number.
>
> You can hide the extra columns if you wish, of course, or put them away on a
> separate sheet - or anyway exclude them from a print range, as you are
> unlikely to want the intermediate values printed.
>
> I'm attaching (perhaps only for the questioner to receive?) a sample
> spreadsheet. The years and book numbers are random, so you can press F9
> repeatedly to see how it operates on different data.
>
> I trust this helps.
>
> Brian Barker
> <Max and min years by book number.ods>
--
To unsubscribe e-mail to: [email protected]
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
