Report Max and Min values from columns in excel which also may have "#NULL!"-CodePudding

I want Excel to place the maximum and minimum of 4 columns into a high and low column. However, my data include #NULL! for some rows/columns. If I just use MAX or MIN, the Excel populates the #NULL! and errors. How can I do conditional formatting to remove the "#NULL!" as an option that Excel is looking at and still get a high or low?

Data example:

CodePudding user response：

Use AGGREGATE which will ignore errors

Max

=AGGREGATE(14,7,B2:E2,1)

Min

=AGGREGATE(15,7,B2:E2,1)

CodePudding user response：

You can use the maxifs and minifs function

if the data is in range b1:b6

=MINIFS(B1:B6, B1:B6,"<>'#NULL'")

=MAXIFS(B1:B6, B1:B6,"<>'#NULL'")

should give your result