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


thank you for your elaborate answer

It works as expected.


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 

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