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: 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