In this article, we will learn about how to get the Maximum score from the list having error values in Excel 2013.
For instance, we have a list of numbers having some errors in a list. We need to find the MAX number from the list. How can we obtain such values? Excel MAX function generates #NA error, if used. The below mentioned formula does exactly the same and get's the maximum value from a list, ignoring all errors.
function - number, type of function to perform on array.
option - number, type of criteria to apply on array.
Array - list of scores or numbers.
|0||Ignore nested SUBTOTAL and AGGREGATE functions|
|1||Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows|
|2||Ignore nested SUBTOTAL, AGGREGATE functions, and error values|
|3||Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows & error values|
|5||Ignore hidden rows|
|6||Ignore error values|
|7||Ignore hidden rows and error values|
The above two tables are guide to function and option selection.
Let’s see how can we get max values while ignoring errors, using it as an example.
Here we have a Data Std_ID range from C3:C18 & a list of their respective scores in range D3:D18 as shown in the snapshot below.
We need to use the formula for the above stated problem.
Use the formula:
The maximum of scores in list is 97. As you can see the formula returns max score from the list having error values.
As you must be working on Excel 2013 till. But if you working in EXCEL 2016 version then you have another option of using the MAXIFS function.
MAXIFS function returns the MAX value having criteria
Use the formula for EXCEL 2016:
As you can see you can get the MAX value from the list using the two functions explained above .
Here are some observational notes as mentioned below.
Hope you understood how to get the Maximum value from the list having errors in Excel. Explore more articles on Excel Count functions here. Please feel free to state your query or feedback for the above article.
How to use MAX function in excel: Use the MAX function to get the Maximum value from the array in Excel.
Calculate max if condition match : Get the MAX value from the array having criteria.
How to Use Excel AGGREGATE Function : Use the AGGREGATE function to perform multiple operation with multiple criterias in excel.
COUNTIFS With OR For Multiple Criteria : Count cells having multiple criteria match using the OR function.
How to use wildcards in excel : Count cells matching phrases using the wildcards in excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.