Re: [libreoffice-users] Question on MAXIF()

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