OpenOffice Calc - how to insert BLANK in a formula?

Question

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.

=IF(A1>B1;"A";"B")

will display A in C1 if the value in A1 exceeds the value in B2, otherwise it will display B. Likewise a cell containing a formula can be made to appear blank with something like this, which inserts an empty string when the cell should appear blank.

=IF(A1>B1;"A";"")

The problem is that this "blank" value can't be tested using the ISBLANK() function in another cell as ISBLANK() on a cell containing such a formula returns false no matter which of the values is displayed. My question is thus, what can be inserted into the true or false leg of an IF() function so that it will allow ISBLANK() on the cell to return true?

In other words, if the formula in C1 is

=IF(A1>B1;"A",X)

what value of X will result in the formula =ISBLANK(C1) being true?

Solution

No value will make isblank return true, because C1 will always contain a formula, and isblank literally tests for blanks. Not empty strings, but actual empty cells.

I think you'd need to use another if -

if(c1="";"one thing";"or another")

Comments

[Steve Crane]

Yes, I realise it can be done that way, but was hoping that there was a way to do it with ISBLANK().

[Paul]

I think the way you would need to go about using isblank() would be to have a script that literally set another cell to blank or not blank depending on what the outcome of this formula. A bit clunky of course. http://wiki.services.openoffice.org/wiki/Documentation/How_T‌​os/Calc:_ISBLANK_fun‌​ction