Color formatting issues - I want zero formatting in cell containing text?

[Elijah]

Hi all,

I have made a dummy file/sheet of larger sheet I've set up. Basically, I have a sheet with 'raw data' which is updated from another source with copy and paste. Linked to this sheet is another sheet which pulls data directly from the sheet with raw data.

Often, the raw data sheet might have empty cells.

Referencing an empty cell returns a zero in Excel in my linked sheet. This is not desirable. So, I have worked around this by using an IFERROR formula. The result is a blank cell also in the linked sheet. Nice.

The problem is however that my color formatting seems to include the blank cells also. I think my blank cell basically contains text. I've tried using various formula, but I can't make it to work. This is using the conditional formatting tools built in Excel.

Any advice?

Formatting.png

[tim201110]

change condition to =AND(NOT(ISBLANK(E7)),E7<0) and =AND(NOT(ISBLANK(E7)),E7>0)

[jason.b75]

That will not work. ISBLANK only works with empty cells, not formula blanks.

For negatives use =E7<0

For positives use =N(E7)>0

What about genuine zero values in the raw data as opposed to empty cells?

[Fluff13]

How about
=AND(E7>0,E7<>"")
=AND(E7<0,E7<>"")

[FlameRetired]

Another.

=AND(ISNUMBER(E7),E7<0)
=AND(ISNUMBER(E7),E7>0)

[jason.b75]

I should have thought of this sooner,

First condition negative numbers, stop if true must be checked.

=E7<0

Second condition positive numbers

=E7

No blank checks needed.

[Elijah]

Excellent! Thank you so much for all contributions. I added a reputation to all of you, but went with Fluff13's solution. Very elegant and efficient.

[jason.b75]

There is no right or wrong way to do what you need, any of the suggestions will do it.

In terms of efficiency, my first suggestion in post #3 uses 3 calculations per cell. Fluff and Flame's suggestions both use 4 calcs per cell, my follow up suggestion when something obvious hit me right between the eyes only uses 1 calc per cell for negatives or 2 for anything else.

It might look to simple to work but it does

[Fluff13]

Glad we could help & thanks for the feedback

[Elijah]

There is no right or wrong way to do what you need, any of the suggestions will do it.

In terms of efficiency, my first suggestion in post #3 uses 3 calculations per cell. Fluff and Flame's suggestions both use 4 calcs per cell, my follow up suggestion when something obvious hit me right between the eyes only uses 1 calc per cell for negatives or 2 for anything else.

It might look to simple to work but it does

Hi Jason,

Your post about efficiency caught my attention. I have trouble with a large sheet of mine crashing due to conditional formatting, so I'd like to try your formula.

However, I can't make it work?

It works for negatives, but using the simple =E7 for positives, I get green cells on the entire column...

Can you help?

Alternatively add the formula in the sheet I uploaded and reupload it in case there are some formula differences between Norway and England? I know there can be some separators that can be different.

Thanks in advance!

Elijah

[jason.b75]

The formula in this case would be the same for both versions, there are no separators or functions to translate incorrectly.

2 things to check, first that the 'Stop if True' box is checked for the negatives. If it is not, the negatives will be formatted as positives.

Second that the rule is looking at the correct cell, when you apply the rule, the formula should refer to the active cell, it is best if the top left cell of the range to which the rule is being applied is active, otherwise things can go wrong quite easily.

[Elijah]

2 things to check, first that the 'Stop if True' box is checked for the negatives. If it is not, the negatives will be formatted as positives.

This seems to have been the error.

Worked out fine now. Thanks a lot, man!