iserror avoid div/0! in max formula

[largeselection]

Hi,

This may or may not be a simple question. Basically I have a column that asks for the max in a row of data. Then I have a conditional format that says if it's equal to the info in the max column then bold that cell. However, I have some where in the rows of data I have Div/0! errors, so the max formula shows Div/0!

Is there a way to write the formula so that I can have the max formula ignore the cells that are Div/0! so that I can get basically the max of the values?

 

[pgc01]

Hi

For ex., select A1:A100 and enter this formula in the conditional formatting:

=A1=MAX(IF(NOT(ISERROR($A$1:$A$100)),$A$1:$A$100))

The cells with the maximum value will be highlighted even if you have cells with errors in the range.

 

[Randombard]

Or change your max formula to be

=MAX(IF(ISERROR(K20:O20),"",K20:O20))

You will need to change the ranges and please note that this is an array formula so you will need to press CTRL+Shift+enter You can tell if you have dont this right as the formula will be wrapped in {}

 

[largeselection]

Hello,

Thanks for all your responses. I ended up going with the following:

=MAX(IF(ISERROR(C15),0,C15),IF(ISERROR(D15),0,D15),IF(ISERROR(E15),0,E15),IF(ISERROR(F15),0,F15),IF(ISERROR(G15),0,G15),IF(ISERROR(H15),0,H15),IF(ISERROR(I15),0,I15))

 

[Aladin Akyurek]

Hello,

Thanks for all your responses. I ended up going with the following:

=MAX(IF(ISERROR(C15),0,C15),IF(ISERROR(D15),0,D15),IF(ISERROR(E15),0,E15),IF(ISERROR(F15),0,F15),IF(ISERROR(G15),0,G15),IF(ISERROR(H15),0,H15),IF(ISERROR(I15),0,I15))

In CF with the Formula Is option...

=C$15=MAX(IF(ISNUMBER(C$15:I$15),C$15:I$15))

Outside CF, if the Max value is needed in some cell:

Control+shift+enter, not just enter...

=MAX(IF(ISNUMBER(C$15:I$15),C$15:I$15))

Note that these two suggestions are one way or other have already been made.