Replies

Steve,
I have replied several times but they are not showing.
Your formulas worked fine. Thank you.
How would I get a string return ” no start date” in column C only if I had a start date, column A, but no end date, column B. In my example, C11 would return “no start date”, but C12 [and all further down] would return a null string.
My CF question and related null string vs “0” has been figured out. Excel adds a set of quotes to the value for “cell value is equal to” and that messed up my CF. My null string, “” ended up, “”””.

How would I format to get a return on the blank “VLOOKUP” cell [K4 or I4], such as “no return” so it would indicate it is not an “empty” cell, but rather one that did not get a return [blank] from the formula? What is the return when blank, “0” or a null string? If I wanted to Conditional format the blank cell [K4, I4] how would I do it?

How would I write the Average formula if the # days where not calculated [column C], that is , if column C did not exist?

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

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.

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?

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.

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?

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.

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.

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.

Rory,
Thanks.

Steve,
Never mind, I figured it out. Thanks.

Tim, Tom, Steve, Thanks.

Tim, your suggestion to eliminate $ worked fine.

Steve, How would I incorporate your formula into my modified formula?
=IF(ISERROR(DATEDIF(B10,B11,”d”)),”no entry”&” “&”B”&CELL(“row”,B10),DATEDIF(B10,B11,”d”))

Thanks.

Anyway to eliminate the $s ?

Thanks SteveA.
That is what I wanted, but did not know how to write the formula. Once I see it it is obvious. Since I wanted to carry an original balance in C3, I just added that to the formula. Works as needed.
=IF(B4=””,””,SUM($B$3:B4)+($C$3)
I was using =0 and could not get it to work. Now I see I did not want “0” because it would not work with a blank.

I was trying to do the math with C4+B5 [add charge to last balance] to get new balance. =IF(B5=””,””,SUM(C4+B5))
Both work fine.
Again, thanks.