Array formula returns just one number everywhere

[Mandark]

I must be doing something wrong...but I came through all the manuals on the web and I am doing it exactly the same how it should work.

I have a table. And for each person (name) and each level, I want to get the maximum value from the column "number".

So if I select the whole range in column "formula", enter this:

{=MAX(IF(((A2:A18=A2)*(B2:B18=B2))>0,C2:C18))}

it should work, right?
Well, wrong. In every row, it returns the same value 7. Which is the maximum for name "a" and level "3". The reference on the name and level doesn't move.
Instead it should return 7 for the first three rows, then 3 for the next three rows, etc...

name	level	number	formula
a	3	5
a	3	6
a	3	7
a	4	1
a	4	2
a	4	3
b	7	2
b	7	3
b	7	4
c	2	10
c	2	11
c	3	1
c	3	2

<tbody>
</tbody>

Can you please tell me, what am I doing wrong?

Thanks.

 

[steve the fish]

The formula is fine apart from absolution of the ranges:

=MAX(IF((($A$2:$A$18=A2)*($B$2:$B$18=B2)),$C$2:$C$18))

 

[Mandark]

The formula is fine apart from absolution of the ranges:

=MAX(IF((($A$2:$A$18=A2)*($B$2:$B$18=B2)),$C$2:$C$18))

Unfortunately, your formula doesn't work either.
Returns 7 everywhere. Just try it in a sheet.
I am baffled. I don't know, what is wrong there.

 

[thisoldman]

The formula steve the fish gave does work with the data you posted. The formula is a single-cell array formula. Do not select the entire C2:C18 range when pressing Ctrl+Shift+Enter. You only want one cell selected, press C+S+E, then copy the formula to the other cells.

 

[Mandark]

The formula steve the fish gave does work with the data you posted. The formula is a single-cell array formula. Do not select the entire C2:C18 range when pressing Ctrl+Shift+Enter. You only want one cell selected, press C+S+E, then copy the formula to the other cells.

Ah, so that was the problem. Thank you very much guys.

I still don't really understand what difference does this slight change make. Could any of you elaborate on it a bit in two sentences?

 

[thisoldman]

Some functions, such as MAX, MIN, SUM, and AVERAGE, are designed to return a single value from an array of cells. Other functions, such as TREND, LINEST, and TRANSPOSE, return an array and must be entered as array formulas. If the outer function is designed to return a single value, it's a single-cell array formula.

I'm not sure how to explain the difference clearly. I often use Formulas >> Evaluate Formula on the ribbon when an array function doesn't work as I expect. Try Evaluate Formula on =SUM(ROW(A1:A5)) as a normal formula and then as an array formula. You need to turn the ROW function into an array for the formula to return what you expect. ROW(A1:A5) not entered with C+S+E returns a single value. With C+S+E it returns five values.

Then try Evaluate Formula on =AVERAGE(IF(ROW(A1:A5) > 2, ROW(A1:A5), FALSE)) – when there are arrays within an IF, it must be entered with C+S+E.