Conditional Formatting

mykeee

New Member
Joined
Sep 9, 2010
Messages
46
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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) ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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,"")
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top