By using the MAX function, the maximum value in a range can be calculated. However to find the cell with the highest value in a range three functions(Address, Match & Max) will have to be nested. The steps below explain how that can be done.
Applies To: Excel 2003, 2007 and 2010
- The screen shot above will be used for this example
- Select cell E3 and type: =ADDRESS(MATCH(MAX(C:C),C:C,0),3,3)
- The cell with the highest value in the range C3:C16 is thus C14
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email firstname.lastname@example.org and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations