There are situations when we need to find out what is the highest or the lowest value in your table or any array of numbers. And sometimes we may need to determine what is the second or third highest or lowest value in a given range. In all such cases ARRAY formulas are very helpful, so I'm providing some examples of using them.

Let's look first at finding the highest/lowest values in an array/table of numbers. Here is a small sample range of numbers:

To extract the maximum and minimum values from this range we use this kind of array formulas:

**53**

**-9**

If we want to find out also what are the second, third, etc... largest/smallest values within the range, we need to use different array formulas as shown in the following examples.

**2**) Answer:

**50**

**3**) Answer:

**47**,

**2**) Answer:

**-8**

**3**) Answer:

**-7**

**,**

To make the formulas much more flexible we can use cell references instead of **2**, **3** (marked by bolded pink in the formulas above).

__Couple important notes related to those formulas__:

1. The formulas are of ARRAY type so, after typing in, you have to enter them using **CTRL+SHIFT+ENTER** keyboard sequence.

2. Even if there are some *strings* included in the tested range the formulas will provide correct results.

3. If there is any empty cell within the range of __ positive__ values only, you will get

__result as the__

**0**__value, so be careful.__

*minimum*4. If there are multiple largest or smallest numbers in the range, the formulas will find just the __first__ one. To find out, if there are more than one max/min values, you'd need to use **Find** feature provided in Excel ribbon.

## No comments:

## Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.