Copper Contributor

Hello, Im hoping someone could please help me with this formula...




If F2 is X, then this cell will select the contents of a random cell between M12 - M14.


If X is not there, it displays FALSE.


How do I get rid of FALSE and just leave it blank?


I have tried adding ,"" at the end and it does not work.


Please help!

12 Replies

@jamesvinicombe  wrote:  ``I have tried adding ,"" at the end and it does not work.``


Perhaps if you showed us what you typed (copy-and-paste from the Formula Bar), we could explain what you did wrong.


The following should work (copy-and-paste):


=IF(F2="x",INDEX(M12:M14,RANDBETWEEN(1,ROWS(M12:M14)),1) , "" )


(Works for me.)

Youre a star! It worked, I was missing a space after the apostrophies at the end!

Now I have an issue whereby the formula below does not work...


M11 is a cell with a SUM in it.

And the cell that I put the formula in is blank at the moment, any ideas what to do?

Thanks for your help!

@jamesvinicombe  wrote:  ``It worked, I was missing a space after the apostrophies at the end!``


Those are double-quotes.  And the spaces should not matter.  I typed it that way just to improve readability.  Please try again, copy-and-pasting the following:





@jamesvinicombe  wrote:  ``=IF(M11="3","x","")``


[.... removed .... Looks like this was ask-and-answered in separate thead.  Please do not that.  Or if you do, please come back and update this thread.  Otherwise, it wastes people's time.]


PS....  On second thought, maybe I misunderstood your first comment above re ``I was missing a space``.  Obviously, you do not have (unnecessary) spaces in your M11=3 formula.  So you "must" understand that they are optional.


This does not strictly answer his question (though he seems happy enough). I was intrigued by the question because after years of programming Excel, I don't know the answer myself. Your "" does not satisfy ISBLANK. It just appears blank to the human reader.

@ecovonrein  wrote:  ``Your "" does not satisfy ISBLANK. It just appears blank``


Right, and nothing can.  (See below.)


But @jamesvinicombe  did not specifically ask for that. On the contrary, he wrote: ``I have tried adding ,"" [...] and it does not work``.  So I believe I correctly interpreted his question to be:  how can we return "" (the null string)?  And as you say, he seems satisfied with my interpretation.


As to your question....  The problem is:  ISBLANK is a misnomer. It should be ISEMPTY.  That is, in Excel, ISBLANK returns true only if there is no value (constant or formula result) in the cell -- which is the Excel definition of an empty cell.


And that is what the IS functions help page says about ISBLANK, to wit:  ``Value refers to an empty cell.``


(Sadly, MSFT misuses the word "value" to mean either parameter or cell reference.)


But your confusion is understandable.  Excel is imprecise and ambiguous in its use of the terms "empty" and "blank".  For example, in the COUNTBLANK help page, the one-line description is ``count the number of empty cells in a range``.  But a later remark in the help page states ``Cells with formulas that return "" [...] are also counted``.


(And once again, MSFT's choice of words is imprecise.  COUNTBLANK counts any value that is the null string, whether it is a formula result or a constant.  One way to create a constant null string is:  enter the formula ="", then copy-and-paste-value back into the same cell.)


The bottom line is:


1. Use ISBLANK when you want to determine if a cell appears blank because it has no value.


2. Use A1="" when you want to determine if a cell appears blank because it has no value or its value is the null string.


3. Use AND(A1="",ISBLANK(A1)=FALSE) if you want to determine if a cell appears blank only because its value is the null string.  (Very unusual, IMHO,)


And of course, use the null string "", not a string of spaces like " ", when you want a result that appears blank, because it is easier to use test #2 above, which is usually the intended condition,  IMHO.


And as noted above, beware that a cell that appears to be empty because it has no formula might actually contain a constant null string.  That can explain why A1+1 results in #VALUE, even though a (truly) empty cell is interpreted as zero in that context.



Finally, IIRC, Google Sheets has a function that returns a truly "empty cell" result -- a contradiction of terms, IMHO.  Thus, ISBLANK returns TRUE for that cell.  If not Google Sheets, then perhaps Open Office.  I don't recall, and I'm not taking the time double-check.


Also, I am not familiar with Office 365 Excel and all of its oh-so-many iterations and "beta" features.  Perhaps a more-current version of Excel does or will have a similar function.  I don't know.

@JoeUser2004 I am having the same issue with the below formula, showing FALSE when I just want it blank




And the root cause is the same:  you are lacking a value-if-false part.


Perhaps you simply want:


=IF(OR(B250="T2R",B250="T1R"),IF(AND(O250>=24),O250-24,0) , "" )

or simply:

=IF(OR(B250="T2R",B250="T1R"), MAX( O250-24, 0 ) , "" )



But the cell isn't truly blank when you do that.  I like the OP, am trying to get the cell to be truly empty.  If you check the cell with the IF formula with a "" in it with ISBLANK it will report false.


@ImmortalCrabman  wrote:  "I like the OP, am trying to get the cell to be truly empty"


A corollary to the adage "people who don't know history are condemned to repeat it"....  Your issue has been thoroughly addressed in the previous responses in this thread.  Read them.


Hint:  In my response dated Jul 15 2022 05:36 PM, what did I say about the behavior of Excel ISBLANK and the Excel definition of "empty" cell?


But don't take my word for it.  Do a google search for "return empty cell in Excel".


PS....  Apparently I was wrong when I speculated that Google Sheets or Open Office has a function that would cause the cell to behave as if it were truly empty.  By that, I mean:  ISBLANK(A1) and A1=0 would return TRUE, and 1*A1 would not return a #VALUE error.



The problem is, When you create a pivot table, and want to use your date column of your source data as a Row value on the pivot table.

If your Date column has Dates and actual empty cells, the pivot table sees your date as a date, and you can group by things like Year, or Month or Quarter and expand and collapse on that, but if you have any "" values in your date column, the pivot table sees nothing as a date anymore, and all of a sudden, nothing is grouped, and everything is treated as text on the pivot table.

So far the only 2 Bad work-arounds seems to be:

  • After your IF statement created "" as value, manually go and delete all cell values that have value "" so that the cell is actually empty.
  • Write a macro to go delete all cell values if value is "" after changes happened.

Both really not ideal solutions and solution 2 is just an automation really of solution 1
:sad: :cry: :facepalm:


@GerrieGrotman  wrote:  ``The problem is, When you create a pivot table [....] the pivot table sees nothing as a date anymore, [...] and everything is treated as text on the pivot table.``


I'm afraid I know little-to-nothing about PTs.  I suggest that you post a new question and make the subject specifically about null strings in PT data.


Off-hand, I wonder if the following would solve the problem....


Instead of using IF( ... , ""), how about using IF( ... , 0) and the following Custom cell format to make the cell appear to be blank:  [=0]"";m/d/yyyy .


(And is there a way to coerce the PT to ignore zero-valued cells?)


Again, if that is an uneducated misdirection, let's not discuss it here (except to acknowledge that it does not work, for the benefit of other readers).


If you create a new discussion with an appropriate title, you are likely to attract helpers who are knowledgeable about PTs.



What seems to work for me is to put a blank space in the code for the "false" result. For example, if I want the target cell to show * if cell B249 is "X", and an empty cell if cell B249 is not "X", my syntax is =IF(B249="X","*"," ")  Notice the single space between the last two quotes. You of course get an "empty cell" warning, but presumably that's OK, because you wanted it to be empty.