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

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