MAXA function. Get the maximum value, including spaces and boolean values.(Microsoft Excel)

03/28/2024

Japanese version.

The MAXA function is a companion to the MAX function.

The difference is that the MAX function only includes numeric values, while non-numeric values are included under certain rules.

How it works

=MAXA(value1 to 255)
NameOmissionDescription
value1 to 255Required argument.Specify the values of the target.

Non-numeric treatment.

valueResult
String and whitespace string.0
Blank cell.Ignore
TRUE1
FALSE0

Demonstrate

This is an example where blank cells are included. Blank spaces are ignored by both the MAX and MINA functions.

However, the result of a formula or function, or a blank string, is treated as 0.

Comparison of MAX and MAXA functions: Blank cell.
Comparison of MAX and MAXA functions: cells where the formula results in a blank cell.

If a string is included, the MAX function ignores it, but the MAXA function treats it as 0.

The MAX function ignores strings, and the MAXA function treats strings as 0.

The MAX function ignores Boolean values, but the MAXA function treats TRUE as 1 and FALSE as 0.

Using the MAXA function on a Boolean-only column will result in a 1 if there is at least one TRUE value.

Both the MAX and MAXA functions will fail if they contain errors.

Specify other than cell range.

It is also possible to return a range of cells as the result of a function argument. In this case, the maximum value is taken from the result of the function.

List of functions that result in a range of cells.

FunctionCategoryDescription
TEXTSPLITTextDivides text into cells based on a specified character.
XLOOKUPLookup & ReferenceGet data matching the search value.
FILTERLookup & ReferenceGet a list of data that matches the condition.
UNIQUELookup & ReferenceDelete duplicate data in a specified cell range.
SEQUENCELookup & ReferenceCreate sequential numbers in multiple cells.
SORTLookup & ReferenceSort a range of cells by a specified column.
SORTBYLookup & ReferenceSort a cell range by specifying multiple reference columns.
DROPLookup & Referenceelete a specified number of rows and columns from a specified cell range.
TAKELookup & ReferenceObtains a specified number of rows and columns from a specified cell range.
TOCOLLookup & ReferenceArranges a specified range of cells in a single column.
TOROWLookup & ReferenceSort a specified range of cells into a single column.
EXPANDLookup & ReferenceExtended specified cell range.
CHOOSEROWSLookup & ReferenceGet the row at the specified position from the specified cell range.
CHOOSECOLSLookup & ReferenceGet the column at the specified position from the specified cell range.
WRAPROWSLookup & ReferenceSort a range of cells in a single column or row by a specified number of rows.
WRAPCOLSLookup & Reference
Sort a range of cells in a column or row by a specified number of columns.
VSTACKLookup & ReferenceMerge multiple cell ranges in row direction.
HSTACKLookup & ReferenceMerge multiple cell ranges in column direction.
TRANSPOSELookup & ReferenceCreate a cell range with the rows and columns of the specified cell range swapped.

It can also be specified as an array.

=MAXA({-10,1,2,-5})

Multiple values can also be specified simply by separating them with commas.

=MAXA(-10,1,2,-5)

Spill

Spill when using the BYROW or BYCOL function.

=BYROW(A1:C3,LAMBDA(r,MAXA(r)))
Row direction
=BYCOL(A1:C3,LAMBDA(c,MAXA(c)))
Column direction

---

Links

Maximum/minimum values for multiple values or cell ranges.

FunctionImportanceDescription
MINHighGet the smallest number.
MAXHighGet the largest number.
MINAMiddleGet the minimum value, including spaces and boolean values.
MAXAMiddleGet the maximum value, including spaces and boolean values.
MINIFSHighGet the minimum value in a cell that matches multiple criteria.
MAXIFSHighGet the maximum value in a cell that matches multiple criteria.

Microsoft Excel Functions Statistical