Conditional Formatting

[mykeee]

I have a cell (E19) with the following conditional formatting:

Formula Is: =ISTEXT(E19)

Cell Value Is: greater than 10

If either of these conditions are met, the cell background is to be turned red.

Cell E19 has the following formula in it:

=IF(ISNUMBER(Data!E19),Data!E19,"")

... so if cell "Data!E19" has no data in it, a null value will be returned to cell E19.

The problem I have is that if there is a null value returned to E19, the background turns red, even though neither of the conditions is met. Anyone have any idea what I am doing wrong?

 

[Excelestial]

Your quotation marks equate to a text. Use a zero instead.

=IF(ISNUMBER(Data!E19),Data!E19,0)

 

[T. Valko]

I have a cell (E19) with the following conditional formatting:


Cell Value Is: greater than 10

If either of these conditions are met, the cell background is to be turned red.

Cell E19 has the following formula in it:

=IF(ISNUMBER(Data!E19),Data!E19,"")

... so if cell "Data!E19" has no data in it, a null value will be returned to cell E19.

The problem I have is that if there is a null value returned to E19, the background turns red, even though neither of the conditions is met. Anyone have any idea what I am doing wrong?

If by null value you mean the formula blank "", that is a TEXT value so ISTEXT(...) will be TRUE.

Seems kind of odd to be testing for both text and numbers in a single cell.

What is your reasoning in using Formula Is: =ISTEXT(E19) ?

 

[mykeee]

Your quotation marks equate to a text. Use a zero instead.

=IF(ISNUMBER(Data!E19),Data!E19,0)

I was avoiding zeros since that causes me other issues.

Cell E31 has the following formula:

=AVERAGE(E19:E30)

If a zero is returned to any of the above cells (as the result of a blank cell) it skews the average. That being said, if the result is truly zero I don't want that to be ignored.

 

[mykeee]

If by null value you mean the formula blank "", that is a TEXT value so ISTEXT(...) will be TRUE.

Seems kind of odd to be testing for both text and numbers in a single cell.

What is your reasoning in using Formula Is: =ISTEXT(E19) ?

I have a report that I paste into a "DATA" worksheet. The report is the result of some testing I do, and if the tester returns an error, this is in the form of a text error message. I want this text to be flagged. Also, if the result is numerical but above 10 I also want it flagged.

 

[Excelestial]

So that your data isn't skewed, why aren't you offsetting false zeros with a COUNT of values or SUMIF. Your background is turning red because you've told it to. The formula you're using says that if there is not a number in e19, then return a blank space...which is text.

 

[mykeee]

So that your data isn't skewed, why aren't you offsetting false zeros with a COUNT of values or SUMIF. Your background is turning red because you've told it to. The formula you're using says that if there is not a number in e19, then return a blank space...which is text.

Could you give me an example of how I would change the following formula as you've explained above?

=IF(ISNUMBER(Data!E19),Data!E19,"")