# MAX and MIN to ignore ERROR or N/A

I already have a list of auto-generated numbers every few minutes, in which that number fluctuates, but sometimes the number is an ERROR or N/A.

For example my list is:

``````2
6
2
1
#N/A
#N/A
4
5
``````

I want to get the max of this list so I would use `=max(B1:B)` which I’m taking the whole column. But in this situation the max will come out as #N/A. I need it to ignore the #N/A and ho straight to the highest number, in this case, 6.

As of now I’ve been manually deleting the error values, but I wanted to know if there was a better way to go about this.

That being said, is there also a way to delete all the #N/A values in a single sheet with a command or such?

Note, I need to take the whole column, so I can’t just do `=max(B1:B4,B7:B)` skipping the error values, as it’s an auto-generating list and the error values are random, not sure where they will land.

Try these formulas:

Max: `=MAXIFS(B3:B10,B3:B10,"<>#N/A")`

Min: `=MINIFS(B3:B10,B3:B10,"<>#N/A")`

Sample

• thanks this worked, i forgot to put quotes over the #N/A. Thanks
– Dan
Sep 4, 2021 at 9:12
• also why did we need to list the b3:b10 twice?
– Dan
Sep 4, 2021 at 9:13
• You need to read and understand the syntax for `MAXIFS`, it is VERY different to plain `MAX`. MAXIFS syntax: `MAXIFS(range, criteria_range1, criterion1)`. So the first "B3:B10" is the Range to be evaluated, the second "B3:B10" is the Criteria Range, and `"<>#N/A"` is the criterion. You can in fact have several criterion ranges/criteria but that is irrelevant to this question Sep 5, 2021 at 2:06
• got it that makes sense, thanks.
– Dan
Sep 5, 2021 at 3:14