CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
InvisibleInk
InvisibleInk

Reputation: 43

Conditional Formatting in Excel selecting wrong rows when trying to highlight cells greater than 15

I am attempting to highlight cells that are greater than 15. But it's highlighting all of the cells in the row. I have a formula in the row so I do not know if that is messing with it. The formula is:

=IFERROR(IF(E7="","0", IF(NETWORKDAYS(E7,O7,MenuData!$G$3:$G$22)<0, "0", (NETWORKDAYS(E7,O7,MenuData!$G$3:$G$22)-1))),0)

I select the entire column that I want to apply the conditional formatting, select highlight cell rules.> greater than > and then put for it to select cells greater than 15 and to highlight them red. It then highlights everything greater than 15 but also selects the zeros that are 0 due to the if/iferror statements in the formula. Is there something wrong with my formula?

Here is what the column looks like once the conditional formatting is applied:

The column looks like once the conditional formatting is applied

Upvotes: 1

Views: 854

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

Welcome to SO. Excel always treats text values as being greater than any number, for example:

="a">9999999999 -> TRUE

As your formula doesn't return a true zero 0, but a zero text value "0", the logic is still the same:

="0">9999999999 -> TRUE

You can either adjust your original Excel formula to return a numerical 0, e.g. =IFERROR(IF(E7="",0,..., or adjust your Conditional Formatting to apply only to numerical values, e.g. =AND(ISNUMBER(H4),H4>15)

Upvotes: 1

Related Questions