Dear All,

I try to use max function to get the maximum value from data as follows:

Col A Col B Col C
Row1 Name Class Mark
Row2 AA 1 100
Row3 BB 2 55
Row4 CC 1 20
Row5 DD 3 45
Row6 EE 2 95
Row7 FF 3 87
Row8 GG 1 55
Row9 HH 3 89
Row10 II 2 85
Row11 JJ 1 45


Summary/Result:
Class Maximum Mark
1 100===Is there any idea that I could use "modified Max functions "
to get the max mark for each class?
(without any sort/re-sort of sequence
of row data above)

2 95
3 89


Many thanks for any advise and suggestions

Best Regards

PA

You can use MAX as an array formula. In the example, to get the max of class
1 you can enter:
=MAX(($C$2:$C$11)*($B$2:$B$11=1))
Enter the formula with CTRL+SHIFT+ENTER
Changing the =1 at the end will allow you to change the class, you can even
reference a cell there.

Hope this helps,
Miguel.

"PA" wrote:

Dear All,

I try to use max function to get the maximum value from data as follows:

Col A Col B Col C
Row1 Name Class Mark
Row2 AA 1 100
Row3 BB 2 55
Row4 CC 1 20
Row5 DD 3 45
Row6 EE 2 95
Row7 FF 3 87
Row8 GG 1 55
Row9 HH 3 89
Row10 II 2 85
Row11 JJ 1 45


Summary/Result:
Class Maximum Mark
1 100===Is there any idea that I could use "modified Max functions "
to get the max mark for each class?
(without any sort/re-sort of sequence
of row data above)

2 95
3 89


Many thanks for any advise and suggestions

Best Regards

PA

=MAX(IF(B2:B11=2,C2:C11))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"PA" wrote in message
...
Dear All,

I try to use max function to get the maximum value from data as follows:

Col A Col B Col C
Row1 Name Class Mark
Row2 AA 1 100
Row3 BB 2 55
Row4 CC 1 20
Row5 DD 3 45
Row6 EE 2 95
Row7 FF 3 87
Row8 GG 1 55
Row9 HH 3 89
Row10 II 2 85
Row11 JJ 1 45


Summary/Result:
Class Maximum Mark
1 100===Is there any idea that I could use "modified Max functions "
to get the max mark for each class?
(without any sort/re-sort of
sequence
of row data above)

2 95
3 89


Many thanks for any advise and suggestions

Best Regards

PA

Dear Bob and Miquel,

Both of solutions work very well

Many thanks & Best Regards

PA

"Bob Phillips" wrote:

=MAX(IF(B2:B11=2,C2:C11))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"PA" wrote in message
...
Dear All,

I try to use max function to get the maximum value from data as follows:

Col A Col B Col C
Row1 Name Class Mark
Row2 AA 1 100
Row3 BB 2 55
Row4 CC 1 20
Row5 DD 3 45
Row6 EE 2 95
Row7 FF 3 87
Row8 GG 1 55
Row9 HH 3 89
Row10 II 2 85
Row11 JJ 1 45


Summary/Result:
Class Maximum Mark
1 100===Is there any idea that I could use "modified Max functions "
to get the max mark for each class?
(without any sort/re-sort of
sequence
of row data above)

2 95
3 89


Many thanks for any advise and suggestions

Best Regards

PA