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:
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.
Let’s begin:
Step 1: Input the formula =AGGREGATE(
We will see the AGGREGATE Function to find the maximum value in the range.
=AGGREGATE(
Step 2: Input the required values in the formula
The following formula requires function name, options, array or reference, and some other optional things.
For the function name we will select 4th option, i.e. the MAX Function.
(…)4 – MAX
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
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:
=AGGREGATE(4,6,B2:B12)
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.
It’s easy, isn’t it?
Leave a Reply