How to Use Functions to Find Values in Excel - dummies

How to Use Functions to Find Values in Excel

By Stephen L. Nelson, E. C. Nelson

Excel provides functions for finding the largest or smallest values in an Excel data set. These functions include MAX, MAXA, MIN, MINA, LARGE and SMALL.

MAX: Maximum value

The MAX function finds the largest value in your data. The function ignores blank cells and cells containing text or logical values such as TRUE and FALSE and uses the syntax

=MAX(number1,[number2])

If the largest value in the range A1:G500 is 50, the function =MAX(A1:G500) returns the value 50.

You can supply up to 255 arguments to the MAX function.

MAXA: Alternate maximum value

In a fashion similar to the MAX function, the MAXA function also finds the largest value in your data. However, unlike the MAX function, the MAXA function includes logical values and text. The logical value TRUE equals 1, the logical value FALSE equals 0, and text also equals 0. The MAXA function uses the syntax

=MAXA(number1,[number2])

MIN: Minimum value

The MIN function finds the smallest value in your data. The function ignores blank cells and cells containing text or logical values such as TRUE and FALSE and uses the syntax

=MIN(number1,[number2])

If the smallest value in the range A1:G500 is 1, the function =MIN(A1:G500) returns the value 1.

MINA: Alternate minimum value

The MINA function also finds the smallest value in your data, but the MINA function includes logical values and text. The logical value TRUE equals 1, the logical value FALSE equals 0, and text also equals 0. The MINA function uses the syntax

=MINA(number1,[number2])

If the smallest value in the range A1:G500 is 1 but this range also includes text values, the function =MINA(A1:G500) returns the value 0.

LARGE: Finding the kth largest value

You can use the LARGE function to find the kth largest value in an array. The function uses the syntax

=LARGE(array,k)

where array is the array of values and k identifies which value you want the function to return. For example, if you store the values 1, 3, 5, 8, and 9 in the worksheet range A1:A5 and you want the function to return the second largest value, use the following formula:

=LARGE(A1:A5,2)

The function returns the value 8 because that’s the second largest value in the array.

SMALL: Finding the kth smallest value

The SMALL function finds the kth smallest value in an array. The function uses the syntax

=SMALL(array,k)

where array is the array of values and k identifies which value you want to find and have the function return. For example, if you store the values 1, 3, 5, 8, and 9 in the worksheet range A1:A5 and you want the function to return the second smallest value, use the following formula:

=SMALL(A1:A5,2)

The function returns the value 3 because that’s the second smallest value in the array.