How to ignore #REF! and #ERROR! when running MAX formula

[gopnik]

Hello,

I want to highlight the highest value of a column of values from B2 to B26


I use this formula : =$B:$B=max(B:B) with conditional formating, but it doesn't work when the sheet throws
errors;


how can i bypass this?
I checked the existing answers on the forum but couldn't make it work for me


Thanks

 

[gopnik]

solved

=$B:$B=max(IFERROR(B:B))

 

[mrhstn]

You could use the AGGREGATE function instead of MAX

=AGGREGATE(4, 6, B:B)

 

[inactiveuserps07]

@gopnik, I'm surprised that your second formula is working without error.

If you find it gives you trouble, and you have an older version of Excel that doesn't include AGGREGATE():

1. I always find it's best (and fastest) to select only the range being evaluated, in this case only B2:B6.

2. With the range selected, another conditional formula could be entered:

=B2=MAX(IF(ISNUMBER(B$2:B$26),B$2:B$26))

 

[gopnik]

@gopnik, I'm surprised that your second formula is working without error.

If you find it gives you trouble, and you have an older version of Excel that doesn't include AGGREGATE():

1. I always find it's best (and fastest) to select only the range being evaluated, in this case only B2:B6.

2. With the range selected, another conditional formula could be entered:

=B2=MAX(IF(ISNUMBER(B$2:B$26),B$2:B$26))

For some reasons, =B2=MAX(IF(ISNUMBER(B$2:B$29),B$2:B$29))

isn't valid in my google sheet

I have good results with =$B:$B=max(IFERROR(B:B)) so far


Also, another question, how can i highlight the second highest value using conditional formating with google sheet?

 

[Aladin Akyurek]

All of these should work in CF involving an error-ridden range...

=$B2=AGGREGATE(4, 6, B:B)

=$B2=MAX(IF(ISNUMBER(B:B),B:B))

=$B2=MAXIFS(B:B,B:B,"<9.99E+307")

 

[gopnik]

oddly, all the formulas are rejected by the conditional formating window in google sheet

it is worth mentioning i run a importJSON script in the background; maybe this affects the formulas

 

[Aladin Akyurek]

Just tried in Google Sheets. The following works as intended:

=$B2=maxifs($B$2:$B$6,$B$2:$B$6,"<9.99E+307")

 

[inactiveuserps07]

@gopnik, in the future, it would help if you let us know you were using Google Sheets and not Excel (which is assumed on Mr. Excel). Google Sheets does not function the same as Excel in many areas, and I would have run my test in Sheets if I'd known.

Glad you figured it out.