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
--
To unsubscribe e-mail to: users+unsubscribe@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

### Context

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.