conditional format only if output from formula

Topic

New Reply

Formula is =IF(ISERROR(DATEDIF(B5,B6,”d”)),”waiting”,REPT(“I”,C5))

I want to conditional format the cells if there is an output either from REPT or ISERROR or cells in B that have at least 1 date. I tried “cell value is greater than 0”, but it includes cells with no output, those with no dates in either DATEDIF cells[B10 & B11 for example]. How do I exclude these cells so only a cell that would output an error [or in my case, “waiting”] or a REPT output would be included?
It seems if both DATEDIF cells are empty, there would be no output and therefore would not be greater than 0, but it does not work in this case. What does this output [both DATEDIF cells are empty] equal?

If Formula is =IF(ISERROR(DATEDIF(B5,B6,”d”)),”waiting”,DATEDIF(B5,B6,”d”)), then the conditional format “cell value is greater than 0” works. No formating is both DATEDIF cells are empty. Why the difference? I obviously must be missing something here.

Thanks.

Reply | Quote

Replies

I don’t completely understand your request: “I want to conditional format the cells if there is an output either from REPT or ISERROR or cells in B that have at least 1 date.”
What format do you want if it has the error (with waiting result), what for the REPTorder do you want. It will be either one or the other. When should it check for at least one date? If you check that first, the REPT option will never format…

I don’t understand: “How do I exclude these cells so only a cell that would output an error [or in my case, “waiting”] or a REPT output would be included?” Those are the only 2 options with the formula. It will always be one or the other, there is no 3rd option to exclude

As to your question: “It seems if both DATEDIF cells are empty, there would be no output and therefore would not be greater than 0, but it does not work in this case. What does this output [both DATEDIF cells are empty] equal?” If both cells are empty, the datedif results in zero days (0) and is thus not an error and will be the REPT part of the equation. It will appear to be “blank” (a null string actually) if the value of C5 is zero. The length of the string is directly dependent on the value in C5.

And finally: “If Formula is =IF(ISERROR(DATEDIF(B5,B6,”d”)),”waiting”,DATEDIF(B5,B6,”d”)), then the conditional format “cell value is greater than 0″ works. No formating is both DATEDIF cells are empty. Why the difference?” as mentioned, if both are blank then the value is zero and is thus not “greater than 0”.

Note: Blank cells will be treated as dates with a value of 0 and thus is equivalent to Jan 0, 1900 (=Dec 31, 1899) If both are blank the datediff result is zero (not an error). If B5 is blank it will datedif result will be the value in B6 (again, not an error). If B6 is blank you will get an error in datediff since the number of days is less than 0…

Steve

Reply | Quote

Steve,
Sorry for the confusion. Your explanation of the results of DATEDIF and REPT when the dates are blank was very helpful and explains why the same conditional format worked for the column where DATEDIF filled in, but would not work in the column where the REPT filled in.

Back to my question, REPT returns 3 options/possibilities as I understand it.
1-returns x number of “I”s
2- returns “waiting”
3- returns null string which shows as blank

My goal is to have a conditional format be true in the REPT column when the first 2 options are returned, [ x number of “I” and “waiting”], but not the 3rd, [null string which shows as blank].
How do I set up conditional format to include #1 & #2 only. I quess what I am asking, how do I exlude a null string return?
Do I have to change the original formula to give me another return/output [other than null string] when dates are blank. Can it be done both ways?

Hope this is clearer than original post.

Reply | Quote

Your option (3) is a subset of option (1) when x = 0, so I wouldn’t state it is a fundamentally different….

You can set a conditional format (a formula) that checks the value of C5. If =C5 = 0 (or check =Counta(B5:B6)=0), you have your option3. Note: since CF run in order so you want option 3 checked BEFORE option 1 since option 1 will be true if both are blank.

Steve

Reply | Quote

Steve,
I am missing something because I cannot get it working. Attached is example. Please help me condition format it so in this case only D5 to D11 is formatted [or shaded] and D12 to D14 would not be shaded [as in column C], but be excluded.

Reply | Quote

Len – Will this do what you are looking for?

Tim

Reply | Quote

It displays “days” for the same reason as has been pointed out: The VALUE in the cell is zero (0). When formatted as:
## “days”

It will not display the 0 due to the format as ## (the # will not display insignificant zeroes), but the ” days” is still shown. To not show the “days” when the value is equal to zero, you can use the custom format:
## “days”;;

Which will display nothing for a negative or a zero value.

Steve

Reply | Quote

To appear to be blank, could also add a second Conditional Format in column C. Make font white.

see attached.

Reply | Quote

Tim,
Yes, the first conditional format worked as does =Bx>0.
What I do not understand is why =Dx>0 [refer to cell itself] does not work in that it also shades empty cells. Apparently these empty cells do not = 0. D12 refers to or =(B13-B12). If both are empty does this not return a “0”? See Steve’s first reply, “If both are blank the datediff result is zero “. And if it does, why does D12>0 format fail?

Tim and Steve,
My reference to ##”days” was not a query but only an explanation that the cells containing it were not cells with returns and should not be shaded, but thank you for the explanations anyway.
I knew about the white font, but not the double semicolon.

Reply | Quote

Len – Will this work for Column D formatting?

Use whatever colors you want for each condition.

Reply | Quote

Cells with text do not = 0, they are >0. Text is > numbers when used in comparisons, but has a value of 0, when used in a formula that converts the text to a umber (it converts when the formula needs a value)

An EMPTY cell is equal to zero. Don’t mistake a cell with value of a null string (“”) which APPEARS empty to be empty. A null string is a text and is thus >0. Also the custom formatting trick I used only MASKS the value, the cell still has a value of 0 in those cases…

The conditional format for D12 is based on B12 which is blank and is thus NOT a number, it does not use C12 which is the number…

Steve

Reply | Quote

Tim and Steve,
Thanks for your patience, input and explanations. It is filling in gaps in my knowledge and will be helpful in the future.

OK, I finally figured out where I was losing this. It was the REPT returning a string, not a number and that a blank string (or NULL string) is not “0”.
I wanted to use, as an alternative method for learning purposes, the REPT column exclusively without referring to the other columns for their numbers but to do that I needed to deal with strings, as in =LEN, not numbers. Thanks Tim for leading me there.

Although =LEN works, as an alternative, is there a way to indicate or differentiate an empty string (NULL) and a not empty string (not NULL) directly, not indirectly through =LEN? Asked another way, in the example given and only using the REPT column without reference to other data, could we use something other than =LEN?

Reply | Quote

Len – Does the function =ISBLANK() help?

Reply | Quote

You can compare to a null string

=D12=””

explicitly compares to the null string.

You could also check the value of C12 since this is what REPT uses for determining the length of the string…

Note: isblank checks for blanks and will always be false if the cell has a formula. A blank is an empty cell. A null string is a zero length string

Steve

Reply | Quote

…….. is there a way to indicate or differentiate an empty string (NULL) and a not empty string (not NULL) directly, …..

…Note: isblank checks for blanks and will always be false if the cell has a formula. A blank is an empty cell. A null string is a zero length string ….

Try this in Cell E12, =ISBLANK(B12), then copy that formula to Columns F and G of Row 12.
Result should be ….
E12 – TRUE
F12 – FALSE
G12 – FALSE

Len – Please consider posting a newer sample file of the project along with issues that need to be resolved.

Reply | Quote

Try this in Cell E12, =ISBLANK(B12), then copy that formula to Columns F and G of Row 12.
Result should be ….
E12 – TRUE
F12 – FALSE
G12 – FALSE

The FALSE in G12 is my point. Cell D12 looks blank since the rept returns a null, but the cell is NOT blank so will not differentiate null string which was Len’s question.

Steve

Reply | Quote

Steve,
If I understand you correctly you are saying that:
1) blank is different than null string, empty cell vs empty string
2) REPT [D12] returns a null string if reference cell [B12] is blank, not a empty cell
REPT(“I”,B12)
3) ISBLANK will return FALSE if the return from REPT is a null string
See #2 B12 is blank, but D12 is not empty [an empty string]
therefore, ISBLANK [D12] will not work in this situation

See attached spreadsheet. It appears ISBLANK does work and is returning TRUE where “D” shows blank [D12 to D15]
The conditional formatting is opposite or reverse of the previous examples since our arguments are now reverse, blank or “”, not cell >0

Column “D” is fromatted with ISBLANK(Dx)
Column “E” is fromatted with Ex=””

Appears to get same results.

Reply | Quote

1) blank is different than null string, empty cell vs empty string
Yes. An empty cell is blank. An empty string is a string and the cell is thus not blank. A formula in a cell means the cell is not blank no matter what the results. Blank only reports empty cells.

2) REPT [D12] returns a null string if reference cell [B12] is blank, not a empty cell REPT(“I”,B12)
Yes. If B12 is blank, the REPT(“I”,B12) will be calc as REPT(“I”,0) so will result in a string of zero length (which is called a null string).

3) ISBLANK will return FALSE if the return from REPT is a null string
Yes since the cell is not blank since it has something in it (a string).

See attached spreadsheet. It appears ISBLANK does work and is returning TRUE where “D” shows blank [D12 to D15] ….Column “D” is fromatted with ISBLANK(Dx)

No. You used isblank on column B, not Col D. The cond format is =IsBlank(Bx)…

Steve

Reply | Quote

Using the file named isblank test.xls. Can a single function be used to test for a blank cell or null string in Column D?
Try using this in Column D for a Conditional Format =COUNTBLANK(Dx)=1

Reply | Quote

But none of the cells in the D range of interest are blank since they all have formulas. If you want to test blanks you must test Col B…

Steve

Reply | Quote

In the range of interest in Col D, COUNTBLANK will treat the null strings as if they are blank/empty cells. Eventhough the cells contain a formula, COUNTBLANK will count them as empty. No need to test Col B as the function works in Col D.

Reply | Quote

Tim and Steve,
It appears “null string” test [XX=””] also tests and returns TRUE for a blank or empty cell. See E16 & E17 in attached spreadsheet.
The results of XX=”” and countblank seem to return the same results. See column E [null string test] & F [countblank] in attached spreadsheet.
D16, D17, E16, E17 are blank.

My interpretation is
1) null string test returns TRUE with null string and blankcell.
2) countblank returns TRUE with null string and blankcell.

I have no idea how these two differ in what they test or return.

My testing seems to show:
ISBLANK – tests for blanks, not null string
Null string test [xx=””] – tests for blanks and null strings
Countblanks – tests for blanks and null strings
None – test for “0”

Is there any difference between ISEMPTY and ISBLANK?

Reply | Quote

Eventhough the cells contain a formula, COUNTBLANK will count them as empty.

Thanks for the correction. I did not realize this, although I should not be surprised. MS is not really into this “constency” thing…

Steve

Reply | Quote

Is there any difference between ISEMPTY and ISBLANK?

Yes. The biggest being one is for excel the other for VB. Also they take different parameters:

IsBlank is an Excel function that returns TRUE if the parameter is a blank cell.

IsEmpty is a VB function that returns TRUE is the parameter is a variable that has not been initialized. If teh parameter is a range, I think it works the same as IsBlank (nulls are not empty).

Steve

Reply | Quote

Is there a method to determine a null string exclusively, ignoring or returning false for empty or blank cells that are not null strings?
xx=”” and LEN seems to return TRUE for blank[not null string]/empty cells.

Reply | Quote

You can so something like:
=AND(ISTEXT(D12),D12=””)

But if you know the cell has a formula in it, then you can just check for null…

Steve

Reply | Quote

Tim and Steve,
Thanks for all your help and info.

Reply | Quote