• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Excel Tutorials

Microsoft Excel Tutorials

  • Home
  • Tutorials
  • Learn
    • Formulas
    • Power Query
    • Errors
    • Videos
    • Charts
    • Shortcuts
    • Pivot Tables
    • Macros
    • Basics
    • Analysis
  • 240+ Excel Shortcuts
  • Privacy Policy
You are here: Home / Analysis / How to get Max Value ignoring errors in a range?
How to get Max Value ignoring errors in a range?

How to get Max Value ignoring errors in a range?

posted on May 12, 2020

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:

Max-value-ignore-all-errorsDownload
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.

Conditional Formatting in Excel (Step-by-Step Tutorial)

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.

=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.

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:

=AGGREGATE(4,6,B2:B12)

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?

Filed Under: Analysis, Formulas Tagged With: AGGREGATE Formula, get Max Value, max value ignoring errors

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Download 240+ Shortcuts

Microsoft Excel comes with varieties of keyboard shortcuts which are stated below with excel shortcut name and its keys for Windows and Mac.

Categories

  • Basics
  • Formulas
  • Pivot Tables
  • Charts
  • Power BI
  • Analysis
  • Shortcuts
  • Macros
  • Add-Ins

Recent Comments

  • Olivia on Convert Formulas to Values in Excel
  • Pkv on How to Insert Comment In Power Query Steps?
  • Rahul on How to Insert Comment In Power Query Steps?
  • David on How to Insert Comment In Power Query Steps?
  • sofia on How to use Workday Formula in Excel?

Footer CTA

Get Weekly Updates in your mailbox

Get Started Now

Copyright © 2021