1

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.

Improve this question
2

1 Answer 1

Reset to default
2

Try these formulas:

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

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

Sample Screenshot

Improve this answer
4
  • 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
    – Tedinoz
    Sep 5, 2021 at 2:06
  • got it that makes sense, thanks.
    – Dan
    Sep 5, 2021 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.