leave cell unchanged after IF test

[John Keith]

I frequently will do an IF test and if the IF test fails I want to
leave the cell unchanged. What I have done in the past is as follows:

=IF(C1>A$1,C1,"")

where the "" puts a null text string in the cell, simulating a no
change (at least it leaves the cell empty in the view mode.

But sometimes setting the cell contents to "" will cause a problem if
there are further references made to that cell (of course I can't
think of a good example right now.)

Is there a better approach to use instead of setting the cell to ""?




John Keith
(e-mail address removed)

 

[Joerg Mochikun]

I assume that the problems you mentioned are caused by formulas using some
kind of text manupulation, which would choke on a empty strings (I can't
think of an example either).

Solution 1: Use a space(" ") instead of an empty string (""). Looks the same
and may prevent errors.
Solution 2: Use a better formula for your references.
Solution 3: Use a description. If an empty cell means "no change", why don't
you put this into your cell? After all the cell contains the result of an
evaluation and not 'nothing'. If you want this result to be unobtrusive you
could format the text color light gray.

Cheers,

Joerg Mochikun

 

[Pete_UK]

In the other cells which refer to this one, you can put:

=IF(cell="","",your_existing_formula)

Hope this helps.

Pete

 

[John Keith]

In the other cells which refer to this one, you can put:

=IF(cell="","",your_existing_formula)

Good idea. I'll try that next time I come across the issue.


John Keith
(e-mail address removed)

 

[John Keith]

Solution 1: Use a space(" ") instead of an empty string (""). Looks the same

and may prevent errors.

Your comment about formulas that reference a text string and chocking
on a null string I think are right on and this may well solve that
issue. Next time I encounter the issue I'll try this.

Solution 2: Use a better formula for your references.

Like Pete's suggestion in his response!

Solution 3: Use a description. If an empty cell means "no change", why don't
you put this into your cell? After all the cell contains the result of an
evaluation and not 'nothing'. If you want this result to be unobtrusive you
could format the text color light gray

Yes this would also work but I try to eliminate unneccesary clutter to
keep a spreadsheet easily readable.

Thanks for the ideas!



John Keith
(e-mail address removed)