software

Dear All,

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

Col ACol BCol C
Row1NameClassMark
Row2AA1100
Row3BB255
Row4CC120
Row5DD345
Row6EE295
Row7FF387
Row8GG155
Row9HH389
Row10II285
Row11JJ145Summary/Result:
ClassMaximum Mark
1100===gt;Is there any idea that I could use quot;modified Max functions quot;
to get the max mark for each class?
(without any sort/re-sort of sequence
of row data above)

295
389Many 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.

quot;PAquot; wrote:

gt; Dear All,
gt;
gt; I try to use max function to get the maximum value from data as follows:
gt;
gt; Col ACol BCol C
gt; Row1NameClassMark
gt; Row2AA1100
gt; Row3BB255
gt; Row4CC120
gt; Row5DD345
gt; Row6EE295
gt; Row7FF387
gt; Row8GG155
gt; Row9HH389
gt; Row10II285
gt; Row11JJ145
gt;
gt;
gt; Summary/Result:
gt; ClassMaximum Mark
gt; 1100===gt;Is there any idea that I could use quot;modified Max functions quot;
gt; to get the max mark for each class?
gt; (without any sort/re-sort of sequence
gt; of row data above)
gt;
gt; 295
gt; 389
gt;
gt;
gt; Many thanks for any advise and suggestions
gt;
gt; Best Regards
gt;
gt; 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)

quot;PAquot; gt; wrote in message
...
gt; Dear All,
gt;
gt; I try to use max function to get the maximum value from data as follows:
gt;
gt; Col A Col B Col C
gt; Row1 Name Class Mark
gt; Row2 AA 1 100
gt; Row3 BB 2 55
gt; Row4 CC 1 20
gt; Row5 DD 3 45
gt; Row6 EE 2 95
gt; Row7 FF 3 87
gt; Row8 GG 1 55
gt; Row9 HH 3 89
gt; Row10 II 2 85
gt; Row11 JJ 1 45
gt;
gt;
gt; Summary/Result:
gt; Class Maximum Mark
gt; 1 100===gt;Is there any idea that I could use quot;modified Max functions quot;
gt; to get the max mark for each class?
gt; (without any sort/re-sort of
sequence
gt; of row data above)
gt;
gt; 2 95
gt; 3 89
gt;
gt;
gt; Many thanks for any advise and suggestions
gt;
gt; Best Regards
gt;
gt; PA
Dear Bob and Miquel,

Both of solutions work very well

Many thanks amp; Best Regards

PA

quot;Bob Phillipsquot; wrote:

gt; =MAX(IF(B2:B11=2,C2:C11))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;PAquot; gt; wrote in message
gt; ...
gt; gt; Dear All,
gt; gt;
gt; gt; I try to use max function to get the maximum value from data as follows:
gt; gt;
gt; gt; Col A Col B Col C
gt; gt; Row1 Name Class Mark
gt; gt; Row2 AA 1 100
gt; gt; Row3 BB 2 55
gt; gt; Row4 CC 1 20
gt; gt; Row5 DD 3 45
gt; gt; Row6 EE 2 95
gt; gt; Row7 FF 3 87
gt; gt; Row8 GG 1 55
gt; gt; Row9 HH 3 89
gt; gt; Row10 II 2 85
gt; gt; Row11 JJ 1 45
gt; gt;
gt; gt;
gt; gt; Summary/Result:
gt; gt; Class Maximum Mark
gt; gt; 1 100===gt;Is there any idea that I could use quot;modified Max functions quot;
gt; gt; to get the max mark for each class?
gt; gt; (without any sort/re-sort of
gt; sequence
gt; gt; of row data above)
gt; gt;
gt; gt; 2 95
gt; gt; 3 89
gt; gt;
gt; gt;
gt; gt; Many thanks for any advise and suggestions
gt; gt;
gt; gt; Best Regards
gt; gt;
gt; gt; PA
gt;
gt;
gt;