Find the max number ignoring errors in Excel

When working with Excel, errors can have a bigger effect than just not looking nice. They can also affect later calculations. If I have a list of cells, and some have errors in them (DIV or NA type errors), how can you find the maximum number ignoring the errors.

So below we have a list with #N/A errors in it. In C22 we have tried to create a MAX function to see what the biggest number in column C is. Due to the existence of a single error (this column has many but even just one will do it), the answer is shown as a #N/A error.

Find the max number ignoring errors in Excel

You could go and fix each cell so that it shows something other than an error message, but you can also use the AGGREGATE function.

So below we have started typing the AGGREGATE function. The first criteria is the function number. In this case the dropdown shows that 4 is the correct number as it is a MAX function.

Find the max number ignoring errors in Excel

After the comma, the next dropdown gives you a number of options. In this case we want the 6- Ignore Error Values but depending on what you are doing you may want to use one of the other options.

Find the max number ignoring errors in Excel

Lastly you must tell it what cells to look for the maximum in.

Find the max number ignoring errors in Excel

When you click enter, you will see that it gives the answer because it ignores all the error values in the range of cells.

Find the max number ignoring errors in Excel