MAX and MAXA functions in Excel for analyzing maximum values

MAX function is used to find the largest number in the range passed in as arguments and returns the corresponding value.

MAXA function is used to find the highest value in the specified range and returns the number found.

The main difference between the two functions is that MAX ignores the logical values passed as arguments, and MAXA takes them into account in the search process.



Examples of using functions of MAX and MAXA in Excel

Example 1. An enterprise economist records income and expenses in a single column of an Excel spreadsheet, with income being positive numbers and expenses being negative. Find the maximum expense for the last few days.

Initial data:

Example 1.

For calculation, we use the array formula:

Argument Description:

  • B3:B12<0 - condition for checking whether a number belongs to a range of negative numbers;
  • ABS(B3:B12) is the return value of the IF function for the found negative numbers.
  • (-1) - the multiplier required to return a negative number.

Note: the largest number in the range of negative numbers is, the value of which is closer to zero. In this problem, we were interested in the maximum flow rate, so for the search for the maximum value, the ABS function was used, which returns the modulus of the number.

Result of calculations:

using functions of MAX.

As a result of calculations by the formula, we obtained the maximum amount of expenses using the MAX function, despite the fact that this is a negative number with a minus sign.



Calculation of the maximum and minimum costs in the Excel spreadsheet

Example 2. The owner of a card connected to Internet banking, has entered into the Excel spreadsheet information about cash receipts on the card, as well as costs. By mistake, some of the cells in the column containing the amount of money got text data. Determine the minimum and maximum transaction costs of funds.

Source data table:

Example 2.

The formula for calculating the minimum cost (array formula):

Calculation of the min.

Argument Description:

  1. NUMBERVALUE(SUBSTITUTE(B3:B8,"USD","")) <0 is an expression that checks the belonging of numbers to a range of negative values. The FAST function replaces part of the string “USD” with an empty value “”, the CHARGE function converts the text data type to a numeric one.
  2. NUMBERVALUE(SUBSTITUTE(B3:B8,"USD","")) - the range of negative numbers, that is, records only of the costs on the card.

To obtain the highest value of costs, we slightly modify this formula:

The maximum flow rate corresponds to the largest modulus of a negative number (the ABS function is used for this purpose). To return a negative number, the result is multiplied by -1.

The resulting values are:

Calculation of the maximum.

Finding the maximum value among different data types in Excel

Example 3. A programmer entered the values of variables of different data types into an Excel spreadsheet. Determine the highest value given the data of the logical type.

Source table:

>Example 3.

In this case, it is advisable to use the MAXA formula, since some variables contain data of a logical type. The formula for calculating:

MAXA.

The result:

Finding the maximum value.

Pay attention to some features of the functions. For example, if in the framework of this example it was required to return a reference to a cell containing the maximum value, you could use the formula:

However, the return value is not true:

However.

But in the case of using the function MAX, ignoring logical values, the result will be correct:

Result:

Results.

Features of using functions of MAX and MAXA in Excel

The MAX function has the following syntax notation:

=MAX(number1,[number2],...)

Argument Description:

  1. number1 is a required argument characterizing the first value of the range of numeric data (array, vector), among which you need to find the maximum value.
  2. [number2] ... - the second and subsequent optional arguments characterizing the second and subsequent numerical values of the studied range.

The MAXA function has the following syntax notation:

=MAXA(value1,[value2],...)

Argument Description:

  • value1 is an obligatory argument characterizing the first occurrence of the range of the studied data.
  • [value2] ... - the second and subsequent optional arguments characterizing the second and subsequent occurrences.

Notes:

  1. Both considered functions accept as arguments names, data of numerical, logical, reference and text data types.
  2. The MAX function takes into account logical values only if they are explicitly passed as an argument (for example, (TRUE; -5; FALSE) returns the value 1, but the arguments (A1; A2; A3) return the value -5 if A1 = TRUE , A2 = -5, A3 = FALSE). The MAXA function in calculations takes into account even references to cells containing data of a logical type.
  3. When using functions for Date data, the maximum value will be returned in the Excel time code.
  4. If the array or data range passed as arguments contains only text data, the result of the MAX and MAXA functions is 0. If the array or the data range also contains text values and empty cells, the MAX and MAXA functions ignore in the calculations.
  5. The MAXA function is convenient for use in cases where it is necessary to transmit a reference to a data range containing textual representations of numbers and logical values that must be taken into account in the calculations. Otherwise, use the MAX function.
  6. Since the functions in question have no analogs with a logical check (for example, the SUM function has an analog with the SUMIF test), to check the conditions, use the following record =MAX(IF(checked_expression,array1,array2)), where array1 and array2 are options for the function arguments MAX depending on the result of executing the checked expression).

Download examples MAX and MAXA functions in Excel

The MAX and MAXA functions can be used as array formulas, which is convenient when used together with logical functions.


en ru