If you are dealing with loads of data and you need to know the maximum value in the range, but there many errors present in the range too. This is what you can do:

get Max Value

As you can see in the image above, we have taken a lot of values in a range. The following range contains numbers as well as errors.

You see, a situation like this can occur when you are dealing with loads of data or you are calculating the overall sales and, in the end, you need to know the product which made the maximum sales.

get Max Value

Let’s begin:

Step 1: Input the formula =AGGREGATE(

We will see the AGGREGATE Function to find the maximum value in the range.


Step 2: Input the required values in the formula

The following formula requires function name, options, array or reference, and some other optional things.

get Max Value

For the function name we will select 4th option, i.e. the MAX Function.

(…)4 – MAX

get Max Value

Then we need to select one of options which we need to apply to our function.

As we want to ignore all errors, so we will be selecting option 6 i.e.

(…)6 – Ignore error values

get Max Value

Then we come to the point of array or reference, this is the point where we swill select the range from which we need to find the maximum value from.

So, in the above example, the same is B2:B12.

Step 3: Cross- check the formula and proceed

By this, our overall formula becomes as follows:


get Max Value

Now all we have to do is press enter and BAAAAMMM!!! The answer is 436. This means that the maximum value present in the range selected is 436, ignoring all the errors.

get Max Value

It’s easy, isn’t it?