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.
Table of contents
Problem- Find the max number ignoring errors in Excel
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.
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.
YouTube MAX ignoring error cells
Ignoring error cells with a MAX calc
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.
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.
Lastly you must tell it what cells to look for the maximum in.
When you click enter, you will see that it gives the answer because it ignores all the error values in the range of cells.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.