WANT TO MAKE A FORMULA RETURN A BLANK CELL (VS 0)

[GEORGE ZEIGLER, SAN DIEGO]

I have tried every method I can think of to return a blank cell as the result
of a formula

EXAMPLES:
IF($D3>1,D3,"") HOPING "" COULD BE INTERPRETED AS EMPTY STRING (BLANK
IF($D3>1,D3,A2) WHERE A2 IS A BLANK CELL

NO MATTER WHAT I TRY, XL AWAYS RETURNS 0'S ... THIS DOESN'T WORK FOR MY
APPLICATION. I CAN'T SEEM TO FIND A WAY TO HAVE A FORMULA RETURN A BLANK
CELL.

ANY HELP IS GREATLY APPRECIATED !!!

THANKS ... GEORGE ZEIGLER, SAN DIEGO.

 

[Max]

First thoughts .. pl remove the caps lock, it's difficult to read

If you mean return a null string ("") if D3 is blank
you could put it like this in say, E3: =IF(ISBLANK($D3),"",D3)

If you mean return a null string ("") if D3 is either blank or contains zero
you could use in E3: =IF($D3>0,D3,"")

 

[GEORGE ZEIGLER, SAN DIEGO]

Thanks Max,

Here's my situation ... I have an alpha string of characters in a cell
directly to the left of the cell which tested for a formula and returned the
value yielded by "". The string of alpha characters is about 20 characters
long, and I need to be able to see the string.

However the string is truncated when it hits the cell where the "" value is
returned ... this makes me unable to see the string ... I'm thinking maybe
since there's a formula in the cell to the right, XL interprets the cell as
"non-empty" and therefore causes the string in the cell to the left to be
truncated. I can't see anything in the "" cell, since it returns a null set,
but it appears the formula itself is causing truncation.

 

[David Biddulph]

If your problem is that the cell to the left isn't wide enough to display
its contents, then you have a number of options:
Widen the column, or
Reduce the font size, or
Format/ Cells/ Alignment, and select Wrap Text

 

[GEORGE ZEIGLER, SAN DIEGO]

David,

Thanks for these thoughts. Unfortunately, I am working with fixed column
widths and text sizes
I have a situation that has 9 adjacent cells in a row ... and the
spreadsheet has several hundred rows ... and I have to be able to print the
spreadsheet ...
there could be data in any one of the nine cells ... if I expand the column
width other cells won't print properly ... and if I shrink down 27 characters
into a small space, I won't be able to read the output when it's printed.

Thanks though for your ideas !!!

-GZ-

 

[Fred Smith]

I would do the following:

1. Highlight all columns you want to print.
2. Position the cursor between two columns, until it turns into a double headed
arrow.
3. Double click. This will adjust all columns wide enough to display all data.
4. Use Page Setup to adjust printing to 1 page wide.
5. Print (or Print Preview)

This will tell you whether all data can legibly print on a page. If it does,
great. If not, you will have to make the decision on how to print everything
legibly -- print landscape, smaller fonts, more pages, etc.

 

[GEORGE ZEIGLER, SAN DIEGO]

Fred,

Thanks for this idea ... that's a really good solution !!!

-GZ-

 

[GEORGE ZEIGLER, SAN DIEGO]

I am grateful to one and all who helped me with this challenge. It ended up
being an extremely interesting theoretical question, which relates to a
fundamental paradox in the way Excel funcions. I ultimately found the
solution by googling the following search:

must contain word "Excel"
must contain phrase "return blank"

this got me the following website, created by Allen Wyatt which offered a
perfect solution:

http://exceltips.vitalnews.com/Pages/T0809_Returning_a_Blank_Value.html

Thanks again to all !!! -GZ-


Returning a Blank Value
Summary: Some people want a formula to return a blank value based upon a
calculation or comparison. Such a condition is impossible in Excel—formulas
can never return blank values. (In fact "blank value" could be considered an
oxymoron, as any value is, by definition, non-blank.) This tip explains the
details of such statements. (This tip works with Microsoft Excel 97, Excel
2000, Excel 2002, and Excel 2003.)
The purpose of formulas is to return a value based upon a calculation or
comparison. For instance, if you use the formula =1+1, the sum is calculated
and the value 2 is returned. There might be times, however, when you want to
use a formula and actually return nothing. For instance, you might want to
compare a value in a cell to some constant, and return a numeric value if
they are equal, or return nothing if they are different.
The problem is that a formula must, under all circumstances, return
something--for it not to do so would go against the very purpose of formulas.
There are two ways to approach the problem, and how you do so will affect
what you can and cannot do with your results. Consider the following formula:
=IF(A1=0,"",1)
In this instance, the cell containing this
• will contain a blank value ("") if A1 is 0
• contain a numeric value (1) if it is 0.
In either case, something is being returned. You could, however, use the
following variation on the formula:
=IF(A1=0,,1)
The only difference here, of course, is that the quote marks have been
removed. Interestingly enough, in this case Excel assumes there is a zero
between the two consecutive commas, and if A1 is 0, the formula returns a 0.
Again, formulas must return something.
The way that Excel's other functions interpret the results of these two
formulas is also very interesting. It is instructive to look at how the
COUNT, COUNTA, and COUNTBLANK functions interpret the results.
COUNT is used to count the number of cells in a range that contain numeric
values. If the cells contain text, or if they are empty, they are ignored. In
the case of our formulas, if you use the first formula, COUNT counts the cell
if A1 is not zero. If you use the second formula, COUNT will always count the
result, since it always returns either 0 or 1, which are both numeric.
COUNTA is used to count the number of cells in a range that contain
anything. Regardless of which formula you use, COUNTA will count the cell
since formulas always return something. (It can also be argued that COUNTA
counts the cell because it contains a formula, but that is probably a fine
semantic difference.)
COUNTBLANK examines cells and counts them only if they are blank. In the
case of the first formula, COUNTBLANK will count the cell only if A1 is 0. In
the case of the second formula, COUNTBLANK will never count the cell, since
the formula always returns a 0 or 1 and is therefore never blank.
The above discussion applies if the COUNT, COUNTA, or COUNTBLANK functions
are evaluating the results of a series of cells that actually contain
formulas. However, if the range includes cells that are really blank (i.e.,
they contain nothing, not even a formula), then that can affect what is
returned by the functions. Blank cells don't affect the results returned by
either COUNT or COUNTA, but they do affect the results returned by COUNTBLANK.
What does all this mean? It means that a cell that contains a formula is
never really, truly blank--only cells with nothing in them are blank. How the
result of the formula is interpreted, however, depends on the Excel functions
being used to perform the interpretation. Since different functions interpret
formula results differently, you need to be concerned with what you really
want to find out about the formula results, and then use the function that
will help you best determine that information. If you don't get the result
you expect with a particular function, search around--chances are good that
Excel has a different function you can use to get the desired results.
That being said,
if you have a range of cells that all contain formulas similar to
=IF(A1=0,"",1), and
you want to delete the formulas in the cells that return a blank value (""),
you can quickly do so by following these steps:
Range / F5 / special / Formulas / text (only box checked) / ok
Excel selects all cells where the formula returned a text value.
all the formulas that returned “â€
Delete.