Question on MAXIF()

users
#1

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)
simular for MAX.

How can I accomplish this?

Any help welcome

Rob.

NVPH nr Jaar Nom. Waarde Serie AlbumBlad Album
g
1 1852 5
1 1
.
2 1852 10
1 1
.
3 1852 15 3 1 1
.
4 1864 5
1 1
.
5 1864 10
1 1
.
6 1864 15 3 1 1
g
7 1867 5
1 1
g
8 1867 10
1 1
.
9 1867 15
1 1
.
10 1867 20
1 1
.
11 1867 25
1 1
.
12 1867 50 6 1 1
.
13 1869 0,5
2 1
.
14 1869 1
2 1
g
15 1869 1
2 1

#2

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

#3

Great,

thank you for your elaborate answer

It works as expected.

Rob.