Modified /advance "Max function "may help!!
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 |
Modified /advance "Max function "may help!!
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 |
Modified /advance "Max function "may help!!
=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 |
Modified /advance "Max function "may help!!
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 |
All times are GMT +1. The time now is 08:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com