What is Max function?

Statistical function is of many categories. One of them is a MAX function. The MAX function returns the largest value from the list of arguments passed to it. MAX function always ignores the empty cells when performing the calculation.

MAX Function Syntax can be Written as Follows:

MAX (arg1, [arg2],……..)

In the syntax, arg1 and arg2 are termed as function’s arguments. First argument is mandatory and all other arguments are optional. Max function can accept a maximum of 255 arguments that has been supported by the Microsoft Excel 2007 and later versions. Before Microsoft Excel 2003, only a maximum of 30 arguments can be passed to the MAX function. These arguments can be listed in the form of ranges, constants or as references to cell.

For accessing MAX function in Excel, enter “=Max(range)” in the cell where the result must be displayed. It determines the maximum of the values specified in the range of cells. There are important points to be remembered, if any value which is directly provided to the MAX function is not a numeric value (non-numeric), then an error namely “#VALUE! Error” will be displayed.

Difference between MAX and MAXA

The MAXA function in Excel displays the highest numerical value from the set of numbers passed to it as arguments. The main difference between MAX and MAXA function is that in MAXA function, true value has been represented by 0 and false value as 1, but MAX function totally ignores the logical as well as text values.

There are some important points to be remembered when using the MAX function:

• It produces “#VALUE!” error if any non-numeric argument is passed to it.
• It ignores blank, logical values like true or false and other text values.
• It returns 0, if no arguments are passed to it.

MAX Function in Excel

It can be used to compute the maximum of the given range of values passed to it. There are two ways to specify the values. The values can be passed as parameters directly or it can be passed as cell references.

The following table shows the input passed as cell references.

The expression “=MAX(A1:A5)” returns 99.

The input can be passed directly as follows.

=MAX(44, 55, 99, 12, 45)

MAXIFS Function

In Excel 2019 and office 365, for finding maximum value, a function named as MAXIFS has been used. MAXIFS function requires three arguments.

• max_range: Input range of numbers
• criteria_range: range for which criteria must be applied
• criteria: criteria to be applies

For example, let use assume that the range D3:D13 consists of the marks and E3:E13 consists of the department then to find the maximum marks in “CSE” department, the MAXIFS function can be specified as follows.

=MAXIFS(D3:D13, C3:C13, “CSE”)

Rules for using MAXIFS function:

• Criteria range must be of the same size as the max_range.
• If criteria_range does not match with the max_range, then MAXIFS returns “#VALUE!” error.

MAX with VLOOKUP Function

VLOOKUP function is used to obtain data from a table organized in a vertical format.

VLOOKUP with MAX function: For returning maximum value from table’s data, the VLOOKUP function can be used with the MAX function.

Let us take an example for clearing the concept of it. First column consists of values such as 1, 2, 3, 4 and 5. Second column consists of colors such as red, green, blue, black and orange. Find out the color having the highest value from the table.

Solution

= VLOOKUP (MAX (A1:A5), A1: B5, 2, FALSE)

The above expression returns “orange”. First max() function is applied which returns 5 and then color name corresponding this value equals “orange”.

General Algorithm for MAX function and MAX function in Python

Algorithm for MAX function can be implemented by using various programming method.

Steps of the algorithm that computes the highest of the given list of numbers:

1. Read the numbers and store it in the list.
2. Assign the first number to max.
3. If any other number in the list is greater than max, then update max.
4. Repeat Step 3 till the list is empty.
5. Return max.

Python function that computes maximum of the given list of numbers.

def findmax(list):

max = list[0]

for num in list:

if num > max:

max = num

return max

We can write the MAX function in Python in any of the two ways as specified below.

• By directly passing the list of values as follows: max (n1, n2, n3, …………..)
• By storing the list of values in iterable such as a list or tuple and passing it to the max function as follow: max (iterable).

The example illustrates how to pass the parameters directly to the max() function.

x = max (“Alice”, “Carol”, “Bob”)

print (x)

The above code displays “Carol” as output.

Let us take another example for better understanding of the max() function in Python. In this example, a tuple is created as passed as argument to the max() function.

c = (6, 22, 25, 94)

print(max(c))

The above code displays the output 94.

The max() function can also be used to return the maximum value from the given list of values. For example,

list = [34,44,55,66]

print(max(list))

The above code displays 66.

Common Mistakes

• The Max() function might be invoked without passing any arguments to it.
• Arguments passed to the Max() function must contain only numeric values. It ignores blank.
• If non-numeric values are passed to it, it produces “#VALUE!” error.
• MAXIFS must be used if a value returned must meet some specified criteria.

Context and Applications   

This topic is significant in the professional exams for both undergraduate and graduate courses, especially for 

• Bachelors in Computer Science  
• Masters in Computer Science