Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Summary
MAX brings back the maximum value from a range of cells. As with other similar functions, it ignores any cells containing text or logical values
As with SUM, it only applies to cells that are formatted as numeric values (including dates, percentages etc. as well as your bog-standard numbers). But if you try to do a maximum across a range that includes some cells containing non-numeric values, it will still work. It will simply ignore those cells in performing its calculation.
If the cells in your range contain the values 1, 2, 3, Y and 4, then the function will return the value 4, the maximum of 1, 2, 3 and 4. The Y will be ignored. Blank cells will be similarly ignored. Similarly, the formula will ignore logical values (i.e. those that contain a TRUE/FALSE value). But cells containing a zero will count towards the calculation. So if the cells in your range contain the values -1, -2, -3, 0, -4, the formula will return the value 0.
Syntax
=MAX(Range)
Range can be made up of a single range or a set of distinct ranges. If you're using a set of distinct ranges, they should be separated with commas.
Examples
=MAX(A1:Q20): this will bring back the highest numeric value between columns A and Q, in rows 1 through 20
=MAX(A1,C5,D12,D15:D20): this will bring back the highest numeric value in the ranges specified
